Created
February 23, 2012 16:13
-
-
Save chanmix51/1893485 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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