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

From 8fb727b4849845fa1bbf733e2a5d015e56820095 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Mon, 3 Nov 2025 17:49:10 -0600
Subject: [PATCH v1] 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      | 69 ++++++++++++++++++++++----
 src/bin/psql/describe.c                |  2 +-
 src/include/catalog/pg_proc.dat        |  3 ++
 src/test/regress/expected/triggers.out | 20 +++++---
 4 files changed, 78 insertions(+), 16 deletions(-)

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..afbca150d5 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);
@@ -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;
 
-	res = pg_get_triggerdef_worker(trigid, pretty);
+	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, PRETTYFLAG_SCHEMA);
 
 	if (res == NULL)
 		PG_RETURN_NULL();
@@ -897,7 +919,7 @@ 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;
@@ -1007,11 +1029,21 @@ 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 ?
+	if (prettyFlags & PRETTYFLAG_INDENT) 
+		appendStringInfoString(&buf, "\n    ");
+	else
+		appendStringInfoString(&buf, " ");
+
+	appendStringInfo(&buf, "ON %s",
+					 (prettyFlags & PRETTYFLAG_SCHEMA) ?
 					 generate_relation_name(trigrec->tgrelid, NIL) :
 					 generate_qualified_relation_name(trigrec->tgrelid));
 
+	if (prettyFlags & PRETTYFLAG_INDENT) 
+		appendStringInfoChar(&buf, '\n');
+	else
+		appendStringInfoString(&buf, " ");
+
 	if (OidIsValid(trigrec->tgconstraint))
 	{
 		if (OidIsValid(trigrec->tgconstrrelid))
@@ -1049,10 +1081,18 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 							 quote_identifier(tgnewtable));
 	}
 
+	if (prettyFlags & PRETTYFLAG_INDENT)
+		appendStringInfoString(&buf, "    ");
+
 	if (TRIGGER_FOR_ROW(trigrec->tgtype))
-		appendStringInfoString(&buf, "FOR EACH ROW ");
+		appendStringInfoString(&buf, "FOR EACH ROW");
+	else
+		appendStringInfoString(&buf, "FOR EACH STATEMENT");
+
+	if (prettyFlags & PRETTYFLAG_INDENT)
+		appendStringInfoChar(&buf, '\n');
 	else
-		appendStringInfoString(&buf, "FOR EACH STATEMENT ");
+		appendStringInfoChar(&buf, ' ');
 
 	/* If the trigger has a WHEN qualification, add that */
 	value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual,
@@ -1066,6 +1106,9 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		RangeTblEntry *oldrte;
 		RangeTblEntry *newrte;
 
+		if (prettyFlags & PRETTYFLAG_INDENT)
+			appendStringInfoString(&buf, "    ");
+
 		appendStringInfoString(&buf, "WHEN (");
 
 		qual = stringToNode(TextDatumGetCString(value));
@@ -1111,7 +1154,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		context.targetList = NIL;
 		context.windowClause = NIL;
 		context.varprefix = true;
-		context.prettyFlags = GET_PRETTY_FLAGS(pretty);
+		context.prettyFlags = prettyFlags;
 		context.wrapColumn = WRAP_COLUMN_DEFAULT;
 		context.indentLevel = PRETTYINDENT_STD;
 		context.colNamesVisible = true;
@@ -1121,9 +1164,17 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 
 		get_rule_expr(qual, &context, false);
 
-		appendStringInfoString(&buf, ") ");
+		appendStringInfoChar(&buf, ')');
+
+		if (prettyFlags & PRETTYFLAG_INDENT)
+			appendStringInfoChar(&buf, '\n');
+		else
+			appendStringInfoString(&buf, " ");
 	}
 
+	if (prettyFlags & PRETTYFLAG_INDENT)
+		appendStringInfoString(&buf, "    ");
+
 	appendStringInfo(&buf, "EXECUTE FUNCTION %s(",
 					 generate_function_name(trigrec->tgfoid, 0,
 											NIL, NULL,
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..7b57394bb3 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
-- 
2.50.1 (Apple Git-155)