gistfile1

BEGIN;
-- is_email
-- Check if the given string is a valid email format or not
-- @param VARCHAR email the string to check
-- @return BOOLEAN 
CREATE OR REPLACE FUNCTION is_email(email VARCHAR) RETURNS BOOLEAN AS $$
BEGIN
      RETURN email ~* e'^([^@\\s]+)@((?:[a-z0-9-]+\\.)+[a-z]{2,})$';
END;
$$ LANGUAGE plpgsql;

-- email type
-- Varchar that verifies the is_email constraint
CREATE DOMAIN email AS VARCHAR CONSTRAINT valid_email CHECK(is_email(VALUE));

CREATE TABLE worker (
  id            serial     PRIMARY KEY,
  email_address email      UNIQUE NOT NULL,
  password      varchar    NOT NULL,
  created_at    timestamp  DEFAULT now() NOT NULL,
  last_login    timestamp
  )
;

CREATE OR REPLACE FUNCTION set_worker() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
  IF NEW.password = OLD.password THEN
    RETURN NEW;
  END IF;
END IF;

NEW.password = crypt(NEW.password::text, gen_salt('bf'));

RETURN NEW;
END;
$$
;

CREATE TRIGGER update_insert_worker BEFORE INSERT OR UPDATE ON worker FOR EACH ROW EXECUTE PROCEDURE set_worker();

COMMIT;