v2-0001-Add-pg_get_trigger_ddl-function.patch

From c4fc505cf7f7612d459f14d43046eeeb0acafa1e Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v2] 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 <paalger0@gmail.com>
---
 doc/src/sgml/func/func-info.sgml       | 46 ++++++++++++++++
 src/backend/utils/adt/ruleutils.c      | 34 ++++++++++++
 src/include/catalog/pg_proc.dat        |  3 +
 src/test/regress/expected/triggers.out | 76 ++++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      | 25 +++++++++
 5 files changed, 184 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}
 
   </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>name</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>. 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.
+       </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..846f3317f7 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,40 @@ 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;
+
+	/* Validate that the relation exists */
+	if (!OidIsValid(relid) || get_rel_name(relid) == NULL)
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_TABLE),
+				 errmsg("relation with OID %u does not exist", relid)));
+
+	/* 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..a2fdd5b38c 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -316,6 +316,82 @@ 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(0, 'modified_a');
+ERROR:  relation with OID 0 does not exist
 -- 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..9d51e27a27 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -192,6 +192,31 @@ 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(0, '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)