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.

Laurenz Albe · Accepted Answer · 2020-02-11 04:54:14Z

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, 2020 at 4:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Morris de Oryx Over a year ago

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: 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.

Collectives™ on Stack Overflow

Building a routine to generate CREATE TRIGGER code in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related