Skip to content

Instantly share code, notes, and snippets.

@bonyiii
Created February 11, 2011 09:24
Show Gist options
  • Save bonyiii/822123 to your computer and use it in GitHub Desktop.
Save bonyiii/822123 to your computer and use it in GitHub Desktop.
PostgreSQL BEFORE INSERT trigger with function
# Function returns user.login, current year, the primary_key which is the id, in 5 length (Example: 00045)
string format
# http://developer.postgresql.org/pgdocs/postgres/functions-formatting.html
# to_char(5,'00000') results the same number format
CREATE OR REPLACE FUNCTION "public"."function_name" () RETURNS trigger AS
'
BEGIN
NEW.title = (SELECT login FROM users WHERE id = NEW.author) || to_char(NOW(),\'YYYY\') || lpad(NEW.id::char, 5, \'0\');
RETURN NEW;
END
'
LANGUAGE 'plpgsql'
# Trigger
CREATE TRIGGER "call_function"
BEFORE INSERT ON table_name FOR EACH ROW
EXECUTE PROCEDURE function_name()
# Show function invoked by trigger name in question in psql command line
select prosrc from pg_trigger,pg_proc where
pg_proc.oid=pg_trigger.tgfoid
and pg_trigger.tgname = '<name>'
# or (only show trigger thtat calls the function)
\d table_name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment