From 7c865a705622aa32fe7a8cc6874bfbc39cb70b43 Mon Sep 17 00:00:00 2001 From: Phil Alger Date: Sun, 5 Oct 2025 08:57:27 -0500 Subject: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement This patch implements the pg_get_trigger_ddl() function, which emits the DDL for CREATE TRIGGER. It includes functionality comments in the code, as well as tests and documentation. The returned function looks like the following: postgres=# SELECT pg_get_trigger_ddl('main_table', 'foo_trigger'); pg_get_trigger_ddl ---------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER foo_trigger BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func() (1 row) PG-152 Author: Phil Alger --- doc/src/sgml/func/func-info.sgml | 46 ++++++++++++++++ src/backend/utils/adt/ruleutils.c | 28 ++++++++++ src/include/catalog/pg_proc.dat | 3 ++ src/test/regress/expected/triggers.out | 74 ++++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 24 +++++++++ 5 files changed, 175 insertions(+) diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index c393832d94..7a24c3e61b 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3797,4 +3797,50 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} + + DDL Retail Functions + + + The functions described in + return the Data Definition Language (DDL) statement for any given database object. + This feature is implemented as a set of distinct functions for each object type. + + + + DDL Retail Functions + + + + + Function + + + Description + + + + + + + + + pg_get_trigger_ddl + + pg_get_trigger_ddl + ( table regclass, trigger_name name ) + text + + + Returns the canonical CREATE TRIGGER statement for the trigger named trigger_name from + its table. The result reflects the stored trigger definition. If the trigger does not exist, + an error is raised. Note that optional, creation-time syntax such as OR REPLACE is not included because it is not + stored in the catalog. + + + + +
+ + + diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 050eef97a4..4be3590e20 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1162,6 +1162,34 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) return buf.data; } +/* ---------- + * pg_get_trigger_ddl - Get the DDL statement for a trigger + * + * This function retrieves the DDL statement for a specified trigger given a + * table name and trigger name. It uses the pg_get_triggerdef_worker function + * to perform the actual retrieval of the DDL statement. This function allows + * users to obtain the DDL definition of a trigger in a convenient manner using + * the trigger's name and the table it belongs to, rather than having to + * look up the trigger OID first to obtain the definition. + * ---------- + */ +Datum +pg_get_trigger_ddl(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + Name trgName = PG_GETARG_NAME(1); + Oid trgOid; + char *res; + + /* Resolve trigger OID */ + trgOid = get_trigger_oid(relid, NameStr(*trgName), false); + + /* pg_get_triggerdef_worker retrieves the trigger definition */ + res = pg_get_triggerdef_worker(trgOid, false); + + PG_RETURN_TEXT_P(string_to_text(res)); +} + /* ---------- * pg_get_indexdef - Get the definition of an index * diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 7c20180637..16cf6d139d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8551,6 +8551,9 @@ { oid => '2730', descr => 'trigger description with pretty-print option', proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid bool', prosrc => 'pg_get_triggerdef_ext' }, +{ oid => '9569', descr => 'get CREATE statement for a trigger', + proname => 'pg_get_trigger_ddl', proisstrict => 't', prorettype => 'text', + proargtypes => 'regclass name', prosrc => 'pg_get_trigger_ddl' }, # asynchronous notifications { oid => '3035', diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 1eb8fba095..d132f85359 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -316,6 +316,80 @@ SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table' CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any') (1 row) +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function with WHEN clauses. +SELECT pg_get_trigger_ddl('main_table', 'modified_a'); + pg_get_trigger_ddl +---------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a') +(1 row) + +SELECT pg_get_trigger_ddl('main_table', 'modified_any'); + pg_get_trigger_ddl +---------------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_any BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.* IS DISTINCT FROM new.*)) EXECUTE FUNCTION trigger_func('modified_any') +(1 row) + +SELECT pg_get_trigger_ddl('main_table', 'insert_a'); + pg_get_trigger_ddl +--------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER insert_a AFTER INSERT ON public.main_table FOR EACH ROW WHEN ((new.a = 123)) EXECUTE FUNCTION trigger_func('insert_a') +(1 row) + +SELECT pg_get_trigger_ddl('main_table', 'delete_a'); + pg_get_trigger_ddl +--------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER delete_a AFTER DELETE ON public.main_table FOR EACH ROW WHEN ((old.a = 123)) EXECUTE FUNCTION trigger_func('delete_a') +(1 row) + +SELECT pg_get_trigger_ddl('main_table', 'insert_when'); + pg_get_trigger_ddl +------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER insert_when BEFORE INSERT ON public.main_table FOR EACH STATEMENT WHEN (true) EXECUTE FUNCTION trigger_func('insert_when') +(1 row) + +SELECT pg_get_trigger_ddl('main_table', 'delete_when'); + pg_get_trigger_ddl +------------------------------------------------------------------------------------------------------------------------------------------ + CREATE TRIGGER delete_when AFTER DELETE ON public.main_table FOR EACH STATEMENT WHEN (true) EXECUTE FUNCTION trigger_func('delete_when') +(1 row) + +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function for CONSTRAINT triggers. +CREATE CONSTRAINT TRIGGER constraint_trig AFTER INSERT ON main_table + DEFERRABLE + FOR EACH ROW EXECUTE PROCEDURE trigger_func('modified_a'); +SELECT pg_get_trigger_ddl('main_table', 'constraint_trig'); + pg_get_trigger_ddl +--------------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE CONSTRAINT TRIGGER constraint_trig AFTER INSERT ON public.main_table DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_func('modified_a') +(1 row) + +DROP TRIGGER constraint_trig ON main_table; +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function with NULL cases. +SELECT pg_get_trigger_ddl(NULL, 'delete_when'); + pg_get_trigger_ddl +-------------------- + +(1 row) + +SELECT pg_get_trigger_ddl('main_table', NULL); + pg_get_trigger_ddl +-------------------- + +(1 row) + +SELECT pg_get_trigger_ddl(NULL, NULL); + pg_get_trigger_ddl +-------------------- + +(1 row) + +-- Fail. Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function for ERROR cases. +SELECT pg_get_trigger_ddl('main_table', 'no_such_trigger'); +ERROR: trigger "no_such_trigger" for table "main_table" does not exist +SELECT pg_get_trigger_ddl('no_such_table', 'modified_a'); +ERROR: relation "no_such_table" does not exist +LINE 1: SELECT pg_get_trigger_ddl('no_such_table', 'modified_a'); + ^ -- Test RENAME TRIGGER ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a; SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 5f7f75d7ba..999ca3cd00 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -192,6 +192,30 @@ SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table' SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function with WHEN clauses. +SELECT pg_get_trigger_ddl('main_table', 'modified_a'); +SELECT pg_get_trigger_ddl('main_table', 'modified_any'); +SELECT pg_get_trigger_ddl('main_table', 'insert_a'); +SELECT pg_get_trigger_ddl('main_table', 'delete_a'); +SELECT pg_get_trigger_ddl('main_table', 'insert_when'); +SELECT pg_get_trigger_ddl('main_table', 'delete_when'); + +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function for CONSTRAINT triggers. +CREATE CONSTRAINT TRIGGER constraint_trig AFTER INSERT ON main_table + DEFERRABLE + FOR EACH ROW EXECUTE PROCEDURE trigger_func('modified_a'); +SELECT pg_get_trigger_ddl('main_table', 'constraint_trig'); +DROP TRIGGER constraint_trig ON main_table; + +-- Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function with NULL cases. +SELECT pg_get_trigger_ddl(NULL, 'delete_when'); +SELECT pg_get_trigger_ddl('main_table', NULL); +SELECT pg_get_trigger_ddl(NULL, NULL); + +-- Fail. Test the output of the pg_get_trigger_ddl(table_name, trigger_name) function for ERROR cases. +SELECT pg_get_trigger_ddl('main_table', 'no_such_trigger'); +SELECT pg_get_trigger_ddl('no_such_table', 'modified_a'); + -- Test RENAME TRIGGER ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a; SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; -- 2.50.1 (Apple Git-155)