v7-0001-Add-pg_get_trigger_ddl-function.patch

From c1d5a7c33baeef820aaf50d206184b0d571b0153 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v7] 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('bar_table', 'foo_trigger');
                                                           pg_get_trigger_ddl
-----------------------------------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER foo_trigger BEFORE UPDATE OF a ON public.bar_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func();
(1 row)

PG-152

Author: Phil Alger <paalger0@gmail.com>
---
 doc/src/sgml/func/func-info.sgml       |  46 ++++++++
 src/backend/utils/adt/ruleutils.c      |  42 ++++++++
 src/include/catalog/pg_proc.dat        |   3 +
 src/test/regress/expected/triggers.out | 140 +++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      |  67 ++++++++++++
 5 files changed, 298 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94..6465989bd7 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}
 
   </sect2>
 
+  <sect2 id="functions-info-retail">
+   <title>DDL Retail Functions</title>
+
+   <para>
+    The functions described in <xref linkend="functions-info-retail-table"/>
+    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.
+   </para>
+
+   <table id="functions-info-retail-table">
+    <title>DDL Retail Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_trigger_ddl</primary>
+        </indexterm>
+        <function>pg_get_trigger_ddl</function>
+        ( <parameter>table</parameter> <type>regclass</type>, <parameter>trigger_name</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Returns the canonical <command>CREATE TRIGGER</command> statement for the trigger named <parameter>trigger_name</parameter>
+        from its <parameter>table</parameter> or relation OID. 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.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 050eef97a4..edb600d798 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,48 @@ 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
+ * relation (or OID) and trigger name.
+ * ----------
+ */
+Datum
+pg_get_trigger_ddl(PG_FUNCTION_ARGS)
+{
+	Oid     relid = PG_GETARG_OID(0);
+	text    *trgName = PG_GETARG_TEXT_PP(1);
+	Oid     trgOid;
+	List   	*nameList;
+	char	*schemaName;
+	char	*objName;
+	char    *res;
+	StringInfoData buf;
+
+	/* Validate that the relation exists */
+	if (!OidIsValid(relid) || get_rel_name(relid) == NULL)
+		PG_RETURN_NULL();
+
+	/* Parse the trigger name to handle quoted identifiers */
+	nameList = textToQualifiedNameList(trgName);
+	DeconstructQualifiedName(nameList, &schemaName, &objName);
+
+	/* Resolve trigger OID */
+	trgOid = get_trigger_oid(relid, objName, false);
+
+	/* pg_get_triggerdef_worker retrieves the trigger definition */
+	res = pg_get_triggerdef_worker(trgOid, false);
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	initStringInfo(&buf);
+
+	appendStringInfo(&buf, "%s;", res);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
 /* ----------
  * 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 b51d2b1737..320c63b8c8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8555,6 +8555,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 text', 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..806349bda6 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -316,6 +316,146 @@ 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');
+                                  ^
+SELECT pg_get_trigger_ddl(-1, 'modified_a');
+ pg_get_trigger_ddl 
+--------------------
+ 
+(1 row)
+
+-- Test the output of a double quoted trigger name with pg_get_trigger_ddl(table_name, trigger_name).
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON main_table
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+-- Fail.
+SELECT pg_get_trigger_ddl('main_table', 'MODIFIED_CAP');
+ERROR:  trigger "modified_cap" for table "main_table" does not exist
+-- Ok.
+SELECT pg_get_trigger_ddl('main_table', '"MODIFIED_CAP"');
+                                                                     pg_get_trigger_ddl                                                                      
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('"MODIFIED_CAP"');
+(1 row)
+
+DROP TRIGGER "MODIFIED_CAP" ON main_table;
+-- Test the output of a double quoted table and trigger name with pg_get_trigger_ddl(table_name, trigger_name).
+CREATE TABLE "FooTable" (a int unique, b int);
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON "FooTable"
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+-- Fail.
+SELECT pg_get_trigger_ddl('FooTable', '"MODIFIED_CAP"');
+ERROR:  relation "footable" does not exist
+LINE 1: SELECT pg_get_trigger_ddl('FooTable', '"MODIFIED_CAP"');
+                                  ^
+SELECT pg_get_trigger_ddl('"FooTable"', 'MODIFIED_CAP');
+ERROR:  trigger "modified_cap" for table "FooTable" does not exist
+-- Ok.
+SELECT pg_get_trigger_ddl('"FooTable"', '"MODIFIED_CAP"');
+                                                                     pg_get_trigger_ddl                                                                      
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON public."FooTable" FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('"MODIFIED_CAP"');
+(1 row)
+
+DROP TRIGGER "MODIFIED_CAP" ON "FooTable";
+DROP TABLE "FooTable";
+-- Test the output of a double quoted schema, table, and trigger name with double quotes.
+CREATE SCHEMA "TestSchema";
+SET search_path TO "TestSchema", public;
+CREATE TABLE "TestSchema"."TestTable" (a int unique, b int);
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON "TestSchema"."TestTable"
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+-- Fail.
+SELECT pg_get_trigger_ddl('"TestSchema.TestTable"', '"MODIFIED_CAP"');
+ERROR:  relation "TestSchema.TestTable" does not exist
+LINE 1: SELECT pg_get_trigger_ddl('"TestSchema.TestTable"', '"MODIFI...
+                                  ^
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', 'MODIFIED_CAP');
+ERROR:  trigger "modified_cap" for table "TestTable" does not exist
+SELECT pg_get_trigger_ddl('TestSchema.TestTable', '"MODIFIED_CAP"');
+ERROR:  relation "testschema.testtable" does not exist
+LINE 1: SELECT pg_get_trigger_ddl('TestSchema.TestTable', '"MODIFIED...
+                                  ^
+-- Ok.
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', '"MODIFIED_CAP"');
+                                                                         pg_get_trigger_ddl                                                                         
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON "TestSchema"."TestTable" FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('"MODIFIED_CAP"');
+(1 row)
+
+DROP SCHEMA "TestSchema" CASCADE;
+NOTICE:  drop cascades to table "TestTable"
 -- 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..e378d703d9 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -192,6 +192,73 @@ 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');
+SELECT pg_get_trigger_ddl(-1, 'modified_a');
+
+-- Test the output of a double quoted trigger name with pg_get_trigger_ddl(table_name, trigger_name).
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON main_table
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+
+-- Fail.
+SELECT pg_get_trigger_ddl('main_table', 'MODIFIED_CAP');
+-- Ok.
+SELECT pg_get_trigger_ddl('main_table', '"MODIFIED_CAP"');
+
+DROP TRIGGER "MODIFIED_CAP" ON main_table;
+
+-- Test the output of a double quoted table and trigger name with pg_get_trigger_ddl(table_name, trigger_name).
+
+CREATE TABLE "FooTable" (a int unique, b int);
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON "FooTable"
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+
+-- Fail.
+SELECT pg_get_trigger_ddl('FooTable', '"MODIFIED_CAP"');
+SELECT pg_get_trigger_ddl('"FooTable"', 'MODIFIED_CAP');
+-- Ok.
+SELECT pg_get_trigger_ddl('"FooTable"', '"MODIFIED_CAP"');
+
+DROP TRIGGER "MODIFIED_CAP" ON "FooTable";
+DROP TABLE "FooTable";
+
+-- Test the output of a double quoted schema, table, and trigger name with double quotes.
+CREATE SCHEMA "TestSchema";
+SET search_path TO "TestSchema", public;
+CREATE TABLE "TestSchema"."TestTable" (a int unique, b int);
+CREATE TRIGGER "MODIFIED_CAP" BEFORE UPDATE OF a ON "TestSchema"."TestTable"
+FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('"MODIFIED_CAP"');
+
+-- Fail.
+SELECT pg_get_trigger_ddl('"TestSchema.TestTable"', '"MODIFIED_CAP"');
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', 'MODIFIED_CAP');
+SELECT pg_get_trigger_ddl('TestSchema.TestTable', '"MODIFIED_CAP"');
+-- Ok.
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', '"MODIFIED_CAP"');
+
+DROP SCHEMA "TestSchema" CASCADE;
+
 -- 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)