[PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

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)

From 14ae85469af766e4ee566282ab5355e9f036a284 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v3] 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      | 31 +++++++++++
 src/include/catalog/pg_proc.dat        |  3 +
 src/test/regress/expected/triggers.out | 76 ++++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      | 25 +++++++++
 5 files changed, 181 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..946a225e6a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,37 @@ 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 (or OID) and trigger name. It uses the pg_get_triggerdef_worker function
+ * to perform the retrieval of the DDL statement.
+ * ----------
+ */
+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)

From 04deded6b875f55693772eaeaaddbd272a2c250e Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v4] 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      | 35 ++++++++++++
 src/include/catalog/pg_proc.dat        |  3 +
 src/test/regress/expected/triggers.out | 76 ++++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      | 25 +++++++++
 5 files changed, 185 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94..9ac652f804 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> 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..cc1962058c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,41 @@ 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);
+	Name    trgName = PG_GETARG_NAME(1);
+	Oid     trgOid;
+	char    *res;
+	StringInfoData buf;
+
+	/* 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)));
+
+	initStringInfo(&buf);
+
+	/* 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);
+
+	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 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..55bb5182b6 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)

From 60b5aa64f54e817e2a31a1108a94f37f8d65097e Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v5] 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      |  40 +++++++
 src/include/catalog/pg_proc.dat        |   3 +
 src/test/regress/expected/triggers.out | 140 +++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      |  67 ++++++++++++
 5 files changed, 296 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..20f34dc301 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,46 @@ 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();
+
+	initStringInfo(&buf);
+
+	/* 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);
+
+	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)

From de1a7d40374b6df5c659be28ea1de8d762084a23 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v6] 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..546978be01 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();
+
+	initStringInfo(&buf);
+
+	/* 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();
+
+	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)

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)

From 7d482c00b790cf6b3f9b765107c9406fb412edc9 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Sun, 5 Oct 2025 08:57:27 -0500
Subject: [PATCH v8] 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      |  47 ++++++++
 src/include/catalog/pg_proc.dat        |   3 +
 src/test/regress/expected/triggers.out | 145 +++++++++++++++++++++++++
 src/test/regress/sql/triggers.sql      |  71 ++++++++++++
 5 files changed, 312 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..b5f6b9b265 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1162,6 +1162,53 @@ 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);
+	if (list_length(nameList) != 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("trigger name cannot be schema qualified")));
+
+	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..d1f073726c 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -316,6 +316,151 @@ 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)
+
+-- Fail. Test the output of adding a schema name to the trigger name
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', '"TestSchema"."MODIFIED_CAP"');
+ERROR:  trigger name cannot be schema qualified
+SELECT pg_get_trigger_ddl('main_table', 'public.modified_a');
+ERROR:  trigger name cannot be schema qualified
+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..92a989d16d 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -192,6 +192,77 @@ 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"');
+
+-- Fail. Test the output of adding a schema name to the trigger name
+SELECT pg_get_trigger_ddl('"TestSchema"."TestTable"', '"TestSchema"."MODIFIED_CAP"');
+SELECT pg_get_trigger_ddl('main_table', 'public.modified_a');
+
+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)