-
-
Save djoudi/0d4d4cc7e3423f4debe0bebea8186005 to your computer and use it in GitHub Desktop.
PostgreSQL BEFORE INSERT trigger with function
This file contains 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
| # 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