v2-0001-Add-pretty-formatting-to-pg_get_triggerdef.patch

From b90a59b7f16353ff33540a98fd6f417fa386436a Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Mon, 3 Nov 2025 17:49:10 -0600
Subject: [PATCH v2] Add pretty formatting to pg_get_triggerdef

This patch adds pretty formatting to the pg_get_triggerdef function. Currently,
when the function is called with the pretty flag, it will not display the schema
and the format is displayed as a single line. This patch modifies this function to
be consistent with functions like pg_get_viewdef, pg_get_ruledef, and pg_get_indexdef,
which display their output as pretty printed SQL. This is an example of the new format:

1. pg_get_triggerdef(oid, true) - New Pretty Format**

postgres=# select pg_get_triggerdef(12345, true);
                pg_get_triggerdef                 
--------------------------------------------------
 CREATE TRIGGER some_trig_foobar AFTER UPDATE    +
     ON some_t                                   +
     FOR EACH ROW                                +
     WHEN (NOT new.some_col)                     +
     EXECUTE FUNCTION dummy_update_func('foobar')
(1 row)


2. pg_get_triggerdef(oid, false) - Existing Compact Format

When pretty printing is false, the output is unchanged from the original function:

postgres=# select pg_get_triggerdef(47901, false);
                                                                 pg_get_triggerdef                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER some_trig_foobar AFTER UPDATE ON public.some_t FOR EACH ROW WHEN ((NOT new.some_col)) EXECUTE FUNCTION dummy_update_func('foobar')
(1 row)

3. New Function: pg_get_triggerdef_compact(oid)

This patch also adds a new function, pg_get_triggerdef_compact, which returns trigger definitions
in a compact, single-line format without schema qualification. This is specifically designed
for the psql \d command.

postgres=# select pg_get_triggerdef_compact(47901);
                                                         pg_get_triggerdef_compact                                                          
--------------------------------------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER some_trig_foobar AFTER UPDATE ON some_t FOR EACH ROW WHEN ((NOT new.some_col)) EXECUTE FUNCTION dummy_update_func('foobar')
(1 row)

This change is due to the \d command's behavior, which shows a table's triggers without schema
qualification. Prior to this patch, pg_get_triggerdef(oid, true) was used because its current
behavior removed the schema name, but at the cost of being un-pretty. The new function allows \d
to use a single-line output that lacks the schema name, while allowing the original function's 
'pretty' output to correctly include the schema name and formatting. Here's an example:

postgres=# \d main_table
             Table "public.main_table"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Indexes:
    "main_table_a_key" UNIQUE CONSTRAINT, btree (a)
Triggers:
    foofoo AFTER INSERT ON main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('foo_bar')
    foo_bar BEFORE INSERT ON main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('foo_bar')
    bar_fooAFTER DELETE ON main_table FOR EACH ROW WHEN ((old.a = 123)) EXECUTE FUNCTION trigger_func('foo_bar')


Author: Phil Alger <paalger0@gmail.com>

---
 src/backend/utils/adt/ruleutils.c      | 115 +++++++++++++++++--------
 src/bin/psql/describe.c                |   2 +-
 src/include/catalog/pg_proc.dat        |   3 +
 src/test/regress/expected/triggers.out |  58 +++++++++++--
 src/test/regress/sql/triggers.sql      |   8 ++
 5 files changed, 143 insertions(+), 43 deletions(-)

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..60af0d4888 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -352,7 +352,7 @@ static char *deparse_expression_pretty(Node *expr, List *dpcontext,
 									   int prettyFlags, int startIndent);
 static char *pg_get_viewdef_worker(Oid viewoid,
 								   int prettyFlags, int wrapColumn);
-static char *pg_get_triggerdef_worker(Oid trigid, bool pretty);
+static char *pg_get_triggerdef_worker(Oid trigid, int prettyFlags);
 static int	decompile_column_index_array(Datum column_index_array, Oid relId,
 										 bool withPeriod, StringInfo buf);
 static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags);
@@ -873,7 +873,7 @@ pg_get_triggerdef(PG_FUNCTION_ARGS)
 	Oid			trigid = PG_GETARG_OID(0);
 	char	   *res;
 
-	res = pg_get_triggerdef_worker(trigid, false);
+	res = pg_get_triggerdef_worker(trigid, 0);
 
 	if (res == NULL)
 		PG_RETURN_NULL();
@@ -886,9 +886,31 @@ pg_get_triggerdef_ext(PG_FUNCTION_ARGS)
 {
 	Oid			trigid = PG_GETARG_OID(0);
 	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	prettyFlags = pretty ? GET_PRETTY_FLAGS(pretty) : 0;
+
+	res = pg_get_triggerdef_worker(trigid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+/*
+ * pg_get_triggerdef_compact
+ *		Returns trigger definition in a compact, single-line format without
+ *		schema qualification designed for the psql \d command.
+ */
+Datum
+pg_get_triggerdef_compact(PG_FUNCTION_ARGS)
+{
+	Oid			trigid = PG_GETARG_OID(0);
 	char	   *res;
 
-	res = pg_get_triggerdef_worker(trigid, pretty);
+	res = pg_get_triggerdef_worker(trigid, PRETTYFLAG_SCHEMA);
 
 	if (res == NULL)
 		PG_RETURN_NULL();
@@ -897,11 +919,12 @@ pg_get_triggerdef_ext(PG_FUNCTION_ARGS)
 }
 
 static char *
-pg_get_triggerdef_worker(Oid trigid, bool pretty)
+pg_get_triggerdef_worker(Oid trigid, int prettyFlags)
 {
 	HeapTuple	ht_trig;
 	Form_pg_trigger trigrec;
 	StringInfoData buf;
+	deparse_context context;
 	Relation	tgrel;
 	ScanKeyData skey[1];
 	SysScanDesc tgscan;
@@ -941,6 +964,19 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 	 * be schema-qualified, but the trigger rel's name may be.
 	 */
 	initStringInfo(&buf);
+	context.buf = &buf;
+	context.namespaces = NIL;
+	context.resultDesc = NULL;
+	context.targetList = NIL;
+	context.windowClause = NIL;
+	context.varprefix = false;
+	context.prettyFlags = prettyFlags;
+	context.wrapColumn = WRAP_COLUMN_DEFAULT;
+	context.indentLevel = 0;
+	context.colNamesVisible = true;
+	context.inGroupBy = false;
+	context.varInOrderBy = false;
+	context.appendparents = NULL;
 
 	tgname = NameStr(trigrec->tgname);
 	appendStringInfo(&buf, "CREATE %sTRIGGER %s ",
@@ -1007,23 +1043,27 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 	 * In non-pretty mode, always schema-qualify the target table name for
 	 * safety.  In pretty mode, schema-qualify only if not visible.
 	 */
-	appendStringInfo(&buf, " ON %s ",
-					 pretty ?
+	appendContextKeyword(&context, " ON ", 0, 0, PRETTYINDENT_VAR - 1);
+	appendStringInfo(&buf, "%s",
+					 (prettyFlags & PRETTYFLAG_SCHEMA) ?
 					 generate_relation_name(trigrec->tgrelid, NIL) :
 					 generate_qualified_relation_name(trigrec->tgrelid));
 
 	if (OidIsValid(trigrec->tgconstraint))
 	{
 		if (OidIsValid(trigrec->tgconstrrelid))
-			appendStringInfo(&buf, "FROM %s ",
-							 generate_relation_name(trigrec->tgconstrrelid, NIL));
+		{
+			appendContextKeyword(&context, " FROM ", 0, 0, PRETTYINDENT_VAR - 1);
+			appendStringInfoString(&buf, generate_relation_name(trigrec->tgconstrrelid, NIL));
+		}
 		if (!trigrec->tgdeferrable)
-			appendStringInfoString(&buf, "NOT ");
-		appendStringInfoString(&buf, "DEFERRABLE INITIALLY ");
+			appendContextKeyword(&context, " NOT DEFERRABLE INITIALLY ", 0, 0, PRETTYINDENT_VAR - 1);
+		else
+			appendContextKeyword(&context, " DEFERRABLE INITIALLY ", 0, 0, PRETTYINDENT_VAR - 1);
 		if (trigrec->tginitdeferred)
-			appendStringInfoString(&buf, "DEFERRED ");
+			appendStringInfoString(&buf, "DEFERRED");
 		else
-			appendStringInfoString(&buf, "IMMEDIATE ");
+			appendStringInfoString(&buf, "IMMEDIATE");
 	}
 
 	value = fastgetattr(ht_trig, Anum_pg_trigger_tgoldtable,
@@ -1040,19 +1080,21 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		tgnewtable = NULL;
 	if (tgoldtable != NULL || tgnewtable != NULL)
 	{
-		appendStringInfoString(&buf, "REFERENCING ");
+		appendContextKeyword(&context, " REFERENCING", 0, 0, PRETTYINDENT_VAR - 1);
 		if (tgoldtable != NULL)
-			appendStringInfo(&buf, "OLD TABLE AS %s ",
+			appendStringInfo(&buf, " OLD TABLE AS %s",
 							 quote_identifier(tgoldtable));
 		if (tgnewtable != NULL)
-			appendStringInfo(&buf, "NEW TABLE AS %s ",
+			appendStringInfo(&buf, " NEW TABLE AS %s",
 							 quote_identifier(tgnewtable));
 	}
 
+	appendContextKeyword(&context, " FOR EACH ", 0, 0, PRETTYINDENT_VAR - 1);
+
 	if (TRIGGER_FOR_ROW(trigrec->tgtype))
-		appendStringInfoString(&buf, "FOR EACH ROW ");
+		appendStringInfoString(&buf, "ROW");
 	else
-		appendStringInfoString(&buf, "FOR EACH STATEMENT ");
+		appendStringInfoString(&buf, "STATEMENT");
 
 	/* If the trigger has a WHEN qualification, add that */
 	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
@@ -1061,12 +1103,12 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 	{
 		Node	   *qual;
 		char		relkind;
-		deparse_context context;
+		deparse_context whenContext;
 		deparse_namespace dpns;
 		RangeTblEntry *oldrte;
 		RangeTblEntry *newrte;
 
-		appendStringInfoString(&buf, "WHEN (");
+		appendContextKeyword(&context, " WHEN (", 0, 0, PRETTYINDENT_VAR - 1);
 
 		qual = stringToNode(TextDatumGetCString(value));
 
@@ -1105,26 +1147,27 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		set_simple_column_names(&dpns);
 
 		/* Set up context with one-deep namespace stack */
-		context.buf = &buf;
-		context.namespaces = list_make1(&dpns);
-		context.resultDesc = NULL;
-		context.targetList = NIL;
-		context.windowClause = NIL;
-		context.varprefix = true;
-		context.prettyFlags = GET_PRETTY_FLAGS(pretty);
-		context.wrapColumn = WRAP_COLUMN_DEFAULT;
-		context.indentLevel = PRETTYINDENT_STD;
-		context.colNamesVisible = true;
-		context.inGroupBy = false;
-		context.varInOrderBy = false;
-		context.appendparents = NULL;
+		whenContext.buf = &buf;
+		whenContext.namespaces = list_make1(&dpns);
+		whenContext.resultDesc = NULL;
+		whenContext.targetList = NIL;
+		whenContext.windowClause = NIL;
+		whenContext.varprefix = true;
+		whenContext.prettyFlags = prettyFlags;
+		whenContext.wrapColumn = WRAP_COLUMN_DEFAULT;
+		whenContext.indentLevel = PRETTYINDENT_STD;
+		whenContext.colNamesVisible = true;
+		whenContext.inGroupBy = false;
+		whenContext.varInOrderBy = false;
+		whenContext.appendparents = NULL;
+
+		get_rule_expr(qual, &whenContext, false);
 
-		get_rule_expr(qual, &context, false);
-
-		appendStringInfoString(&buf, ") ");
+		appendStringInfoChar(&buf, ')');
 	}
 
-	appendStringInfo(&buf, "EXECUTE FUNCTION %s(",
+	appendContextKeyword(&context, " EXECUTE FUNCTION ", 0, 0, PRETTYINDENT_VAR - 1);
+	appendStringInfo(&buf, "%s(",
 					 generate_function_name(trigrec->tgfoid, 0,
 											NIL, NULL,
 											false, NULL, false));
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f2450284..dffaeecc50 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3251,7 +3251,7 @@ describeOneTableDetails(const char *schemaname,
 
 		printfPQExpBuffer(&buf,
 						  "SELECT t.tgname, "
-						  "pg_catalog.pg_get_triggerdef(t.oid, true), "
+						  "pg_catalog.pg_get_triggerdef_compact(t.oid), "
 						  "t.tgenabled, t.tgisinternal,\n");
 
 		/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f7..1c91c18edd 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 => '9750', descr => 'trigger description in compact single-line format',
+  proname => 'pg_get_triggerdef_compact', provolatile => 's', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_triggerdef_compact' },
 
 # asynchronous notifications
 { oid => '3035',
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba095..774d9f1405 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -299,9 +299,13 @@ SELECT * FROM main_table ORDER BY a, b;
 (8 rows)
 
 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
-                                                             pg_get_triggerdef                                                             
--------------------------------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a')
+                pg_get_triggerdef                
+-------------------------------------------------
+ CREATE TRIGGER modified_a BEFORE UPDATE OF a   +
+     ON main_table                              +
+     FOR EACH ROW                               +
+     WHEN (old.a <> new.a)                      +
+     EXECUTE FUNCTION trigger_func('modified_a')
 (1 row)
 
 SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
@@ -311,9 +315,13 @@ SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table
 (1 row)
 
 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
-                                                                      pg_get_triggerdef                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------
- 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')
+                 pg_get_triggerdef                 
+---------------------------------------------------
+ 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 RENAME TRIGGER
@@ -364,6 +372,15 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
  CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
 (1 row)
 
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+                     pg_get_triggerdef                      
+------------------------------------------------------------
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b+
+     ON main_table                                         +
+     FOR EACH ROW                                          +
+     EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+(1 row)
+
 UPDATE main_table SET a = 50;
 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
@@ -2318,6 +2335,24 @@ create constraint trigger parted_trig_two after insert on parted_constr
   deferrable initially deferred enforced
   for each row when (bark(new.b) AND new.a % 2 = 1)
   execute procedure trigger_notice_ab();
+-- Test that the constraint trigger is formatted correctly
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'parted_constr'::regclass and tgname = 'parted_trig_two';
+                   pg_get_triggerdef                    
+--------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT+
+     ON parted_constr                                  +
+     DEFERRABLE INITIALLY DEFERRED                     +
+     FOR EACH ROW                                      +
+     WHEN (bark(new.b) AND (new.a % 2) = 1)            +
+     EXECUTE FUNCTION trigger_notice_ab()
+(1 row)
+
+SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'parted_constr'::regclass and tgname = 'parted_trig_two';
+                                                                                             pg_get_triggerdef                                                                                             
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(1 row)
+
 -- The immediate constraint is fired immediately; the WHEN clause of the
 -- deferred constraint is also called immediately.  The deferred constraint
 -- is fired at commit time.
@@ -2739,6 +2774,17 @@ SELECT trigger_name, event_manipulation, event_object_schema, event_object_table
  parent_update_trig | UPDATE             | public              | parent             |            1 |                  | STATEMENT          | AFTER         | old_table                  | new_table
 (12 rows)
 
+-- Test that the trigger is formatted correctly
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'child3'::regclass AND tgname = 'child3_insert_trig';
+               pg_get_triggerdef                
+------------------------------------------------
+ CREATE TRIGGER child3_insert_trig AFTER INSERT+
+     ON child3                                 +
+     REFERENCING NEW TABLE AS new_table        +
+     FOR EACH STATEMENT                        +
+     EXECUTE FUNCTION dump_insert()
+(1 row)
+
 -- insert directly into children sees respective child-format tuples
 insert into child1 values ('AAA', 42);
 NOTICE:  trigger = child1_insert_trig, new table = (AAA,42)
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba..ad71c91d22 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -230,6 +230,7 @@ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
 
 SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
 
 UPDATE main_table SET a = 50;
 UPDATE main_table SET b = 10;
@@ -1608,6 +1609,10 @@ create constraint trigger parted_trig_two after insert on parted_constr
   for each row when (bark(new.b) AND new.a % 2 = 1)
   execute procedure trigger_notice_ab();
 
+-- Test that the constraint trigger is formatted correctly
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'parted_constr'::regclass and tgname = 'parted_trig_two';
+SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'parted_constr'::regclass and tgname = 'parted_trig_two';
+
 -- The immediate constraint is fired immediately; the WHEN clause of the
 -- deferred constraint is also called immediately.  The deferred constraint
 -- is fired at commit time.
@@ -1907,6 +1912,9 @@ SELECT trigger_name, event_manipulation, event_object_schema, event_object_table
   WHERE event_object_table IN ('parent', 'child1', 'child2', 'child3')
   ORDER BY trigger_name COLLATE "C", 2;
 
+-- Test that the trigger is formatted correctly
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'child3'::regclass AND tgname = 'child3_insert_trig';
+
 -- insert directly into children sees respective child-format tuples
 insert into child1 values ('AAA', 42);
 insert into child2 values ('BBB', 42);
-- 
2.50.1 (Apple Git-155)