Building a routine to generate CREATE TRIGGER code in Postgres

Question

I haven't found a straightforward way to retrieve trigger definition code. I mean the trigger/binding declaration, not the trigger function. I figured I'd use some of the system catalogs to build up a script. The following, incomplete, version produces sensible output:

CREATE OR REPLACE FUNCTION dba.ddl_get_build_trigger_code(trigger_id oid) -- I starting from having the OID.
    RETURNS text

AS $BODY$

DECLARE

trigger_name_in text;
code text;

BEGIN
/*
What the original declaration looks like:

CREATE TRIGGER trigger_hsys_after_delete
    AFTER DELETE
    ON data.hsys
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT
    EXECUTE PROCEDURE data.trigger_function_log_deletion_count();
*/

SELECT tgname FROM pg_trigger WHERE oid = trigger_id INTO trigger_name_in; -- information_schema tables don't use PG OIDs.

RETURN 
'CREATE TRIGGER ' || trigger_name || chr(10) ||
 chr(9) || action_timing || ' ' || event_manipulation || chr(10) ||
 chr(9) || 'ON ' || event_object_schema || '.' || event_object_table ||

 CASE WHEN action_reference_old_table IS NOT NULL THEN
 chr(10) || chr(9) || 'REFERENCING OLD TABLE AS ' || action_reference_old_table || chr(10) END ||

--  CASE WHEN action_reference_new_table IS NOT NULL THEN
--  chr(10) || chr(9) || 'REFERENCING NEW TABLE AS ' || action_reference_new_table || chr(10) END ||

 chr(9) || 'FOR EACH ' || action_orientation || chr(10) ||
 chr(9) || action_statement || ';' as create_trigger_code

  FROM information_schema.triggers 
 WHERE trigger_name = trigger_name_in;

END;
 $BODY$
LANGUAGE plpgsql;

Here's a sample, matching my actual case:

CREATE TRIGGER trigger_hsys_after_delete
    AFTER DELETE
    ON data.hsys
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_function_log_deletion_count();

There are several more attributes in information_schema.triggers that may have values, such as action_reference_new_table. When I enable the lines below and action_reference_new_table is NULL, the the script returns NULL:

CASE WHEN action_reference_new_table IS NOT NULL THEN
chr(10) || chr(9) || 'REFERENCING NEW TABLE AS ' || action_reference_new_table || chr(10) END ||

I don't understand why the NULL value for action_reference_new_table blows up my concatenation code and makes the entire result NULL.

Apart from help on this specific question, feel free to point out whatever I should do to write more sensible PL/PgSQL code. It's proving to be harder for me to master than I would have guessed.

score 1 · Accepted Answer · answered Feb 11 '20 at 04:54

1

Simply use

SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = trigger_name_in;

Besides, never use string concatenation when composing SQL code. The danger of SQL injection is too great. Use the format() function with the %I placeholder.

answered Feb 11 '20 at 04:54

Laurenz Albe

209,280
17
206
263

Beautiful! I was aware of `pg_get_viewdef` and not of `pg_get_triggerdef`. I see that there are several other useful functions for me to use: https://www.postgresql.org/docs/current/functions-info.html No `pg_get_typedef` or `pg_get_castdef`, but that's for another day. And, yes, chastened on `||` instead of `format()`, I'll be strict about it. – Morris de Oryx Feb 11 '20 at 09:49

Building a routine to generate CREATE TRIGGER code in Postgres

1 Answers1