Skip to content

Instantly share code, notes, and snippets.

@chanmix51
Created February 23, 2012 16:13
Show Gist options
  • Save chanmix51/1893485 to your computer and use it in GitHub Desktop.
Save chanmix51/1893485 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment