[PATCH] Add pretty formatting to pg_get_triggerdef
Hello Hackers,
Currently, `pg_get_triggerdef` includes a "pretty" flag, but it does not
actually format the `CREATE TRIGGER` statement in a "pretty" way. Unlike
`pg_get_viewdef`, `pg_get_ruledef`, and `pg_get_indexdef`, the purpose of
pretty formatting has been to remove the schema name so it can be used by
the `\d` psql comment to display triggers associated with a view or table,
as shown below:
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')
This patch introduces true pretty formatting to `pg_get_triggerdef`.
Additionally, it creates a new function specifically for the `\d` psql
command, as that command requires schema removal and a single line
statement.
With this patch, when the `pretty` parameter is set to `true`,
`pg_get_triggerdef` now displays a formatted output, consistent with
`pg_get_viewdef`, `pg_get_ruledef`, and `pg_get_indexdef`:
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)
When the `pretty` flag is `false`, the function's behavior remains
unchanged from the original implementation:
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)
--
Best,
Phil Alger
Attachments:
v1-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchapplication/octet-stream; name=v1-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchDownload
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)
Hi, Philip,
I verified your patch on my Ubuntu 24.04 and it works as expected. The "make check" passed.
Also, the code change looks good to me.
Regards,
Steven
________________________________
From: Philip Alger <paalger0@gmail.com>
Sent: Tuesday, November 04, 2025 11:36
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: [PATCH] Add pretty formatting to pg_get_triggerdef
Hello Hackers,
Currently, `pg_get_triggerdef` includes a "pretty" flag, but it does not actually format the `CREATE TRIGGER` statement in a "pretty" way. Unlike `pg_get_viewdef`, `pg_get_ruledef`, and `pg_get_indexdef`, the purpose of pretty formatting has been to remove the schema name so it can be used by the `\d` psql comment to display triggers associated with a view or table, as shown below:
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')
This patch introduces true pretty formatting to `pg_get_triggerdef`. Additionally, it creates a new function specifically for the `\d` psql command, as that command requires schema removal and a single line statement.
With this patch, when the `pretty` parameter is set to `true`, `pg_get_triggerdef` now displays a formatted output, consistent with `pg_get_viewdef`, `pg_get_ruledef`, and `pg_get_indexdef`:
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)
When the `pretty` flag is `false`, the function's behavior remains unchanged from the original implementation:
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)
--
Best,
Phil Alger
On Nov 4, 2025, at 11:36, Philip Alger <paalger0@gmail.com> wrote:
--
Best,
Phil Alger
<v1-0001-Add-pretty-formatting-to-pg_get_triggerdef.patch>
1
```
+/*
+ * 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);
```I think this is a mis-use of PRETTYFLAG_SCHEMA that is for printing schema-qualified names but omitting schema.
2
```
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ appendStringInfoString(&buf, "\n ");
```We should not hardcode 4 white-spaces, instead, we should use the const PRETTYINDENT_STD. Did you ever consider using appendContextKeyword()? Checking for an existing usage:
```
static void
get_rule_windowclause(Query *query, deparse_context *context)
{
StringInfo buf = context->buf;
const char *sep;
ListCell *l;
sep = NULL;
foreach(l, query->windowClause)
{
WindowClause *wc = (WindowClause *) lfirst(l);
if (wc->name == NULL)
continue; /* ignore anonymous windows */
if (sep == NULL)
appendContextKeyword(context, " WINDOW ",
-PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
else
appendStringInfoString(buf, sep);
appendStringInfo(buf, "%s AS ", quote_identifier(wc->name));
get_rule_windowspec(wc, query->targetList, context);
sep = ", ";
}
}
```
3 Looks like you forgot to update pg_get_triggerdef(), when it calls pg_get_triggerdef_worker(tirgid, false), the second parameter “false” should be updated to an int flag.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Hi Chao,
+/*
+ * 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); ```I think this is a mis-use of PRETTYFLAG_SCHEMA that is for printing
schema-qualified names but omitting schema.
Yes, this is to omit the schema because the functions is used to print out
the triggers when using \d in psql, The current practice isn't to print out
a schema for the table/view/etc.
2
``` + if (prettyFlags & PRETTYFLAG_INDENT) + appendStringInfoString(&buf, "\n "); ```We should not hardcode 4 white-spaces, instead, we should use the const
PRETTYINDENT_STD. Did you ever consider using appendContextKeyword()?
Checking for an existing usage:
```
Thanks for pointing this out. I refactored the code using
appendContextKeyword() and added a few tests as well in v2 (attached).
3 Looks like you forgot to update pg_get_triggerdef(), when it calls
pg_get_triggerdef_worker(tirgid, false), the second parameter “false”
should be updated to an int flag.
I fixed this to set to 0. However, false is defined as 0 in stdbool.h
#define false 0
--
Best,
Phil Alger
EDB: https://www.enterprisedb.com
Attachments:
v2-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchapplication/octet-stream; name=v2-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchDownload
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)
On Nov 5, 2025, at 04:56, Philip Alger <paalger0@gmail.com> wrote:
Hi Chao,
+/* + * 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); ```I think this is a mis-use of PRETTYFLAG_SCHEMA that is for printing schema-qualified names but omitting schema.
Yes, this is to omit the schema because the functions is used to print out the triggers when using \d in psql, The current practice isn't to print out a schema for the table/view/etc.
I guess I didn’t express myself clearly. My comment was that, this function wants to omit schema, so PRETTYFLAG_SCHEMA should not be used here, because it is for adding schema.
3 Looks like you forgot to update pg_get_triggerdef(), when it calls pg_get_triggerdef_worker(tirgid, false), the second parameter “false” should be updated to an int flag.
I fixed this to set to 0. However, false is defined as 0 in stdbool.h
#define false 0
I know false is actually 0, but passing false to an int argument is misleading.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Hi Chao,
I think this is a mis-use of PRETTYFLAG_SCHEMA that is for printing
schema-qualified names but omitting schema.
Yes, this is to omit the schema because the functions is used to print
out the triggers when using \d in psql, The current practice isn't to print
out a schema for the table/view/etc.I guess I didn’t express myself clearly. My comment was that, this
function wants to omit schema, so PRETTYFLAG_SCHEMA should not be used
here, because it is for adding schema.
I am not sure that's right because I am using it in a similar way as what's
written in pg_get_index_worker:
appendStringInfo(&buf, "CREATE %sINDEX %s ON %s%s USING %s (",
idxrec->indisunique ? "UNIQUE " : "",
quote_identifier(NameStr(idxrelrec->relname)),
idxrelrec->relkind == RELKIND_PARTITIONED_INDEX
&& !inherits ? "ONLY " : "",
(prettyFlags & PRETTYFLAG_SCHEMA) ?
generate_relation_name(indrelid, NIL) :
generate_qualified_relation_name(indrelid),
quote_identifier(NameStr(amrec->amname)));
For that function, with `true` set you don't get the schema:
postgres=# select pg_get_indexdef(16395, 0, true);
pg_get_indexdef
--------------------------------------------------------------------
CREATE UNIQUE INDEX main_table_a_key ON main_table USING btree (a)
(1 row)
Similarly, in this patch, you get:
postgres=# \d child3
Table "public.child3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
b | integer | | |
a | text | | |
Partition of: parent FOR VALUES IN ('CCC')
Triggers:
child3_delete_trig AFTER DELETE ON child3 REFERENCING OLD TABLE AS
old_table FOR EACH STATEMENT EXECUTE FUNCTION dump_delete()
Or this without the schema as well:
postgres=# 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)
Setting prettyFlags = PRETTYFLAG_SCHEMA removes the schema.
--
Best,
Phil Alger
EDB: https://www.enterprisedb.com
I am attaching v3.
I changed the name of the function that prints out the trigger using the \d
command (below) from `pg_get_triggerdef_compact` to
`pg_get_triggerdef_string`, which matches the index naming convention for a
similar function.
postgres=# \d child3
Table "public.child3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
b | integer | | |
a | text | | |
Partition of: parent FOR VALUES IN ('CCC')
Triggers:
child3_delete_trig AFTER DELETE ON child3 REFERENCING OLD TABLE AS
old_table FOR EACH STATEMENT EXECUTE FUNCTION dump_delete()
I've cleaned up the code by removing deparse_context variables related to
appendContextKeyword that were set as NULL or NIL. All tests passed.
--
Best,
Phil Alger
EDB: https://www.enterprisedb.com
Attachments:
v3-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchapplication/octet-stream; name=v3-0001-Add-pretty-formatting-to-pg_get_triggerdef.patchDownload
From 1c01dd86e36678ce7f67b65219309fe0ec1809a2 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Mon, 3 Nov 2025 17:49:10 -0600
Subject: [PATCH v3] 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 Format
When pretty printing is false, or no flag is provided, the output is unchanged from the original function:
postgres=# select pg_get_triggerdef(12345, 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_string(oid)
This patch also adds a new function, pg_get_triggerdef_string, which returns trigger definitions
formatted as a single-line without schema qualification. This is specifically designed
for the psql \d command.
postgres=# select pg_get_triggerdef_string(12345);
pg_get_triggerdef_string
--------------------------------------------------------------------------------------------------------------------------------------------
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 | 111 +++++++++++++++++--------
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, 139 insertions(+), 43 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..89b1c57e8e 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,34 @@ 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_string
+ * Returns trigger definition as a string formatted as a single-line
+ * without schema qualification designed for the psql \d command.
+ */
+Datum
+pg_get_triggerdef_string(PG_FUNCTION_ARGS)
+{
+ Oid trigid = PG_GETARG_OID(0);
+ int prettyFlags;
char *res;
- res = pg_get_triggerdef_worker(trigid, pretty);
+ prettyFlags = PRETTYFLAG_SCHEMA;
+
+ res = pg_get_triggerdef_worker(trigid, prettyFlags);
if (res == NULL)
PG_RETURN_NULL();
@@ -897,11 +922,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;
@@ -942,6 +968,12 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
*/
initStringInfo(&buf);
+ context.buf = &buf;
+ context.prettyFlags = prettyFlags;
+ context.wrapColumn = WRAP_COLUMN_DEFAULT;
+ context.indentLevel = 0;
+ context.colNamesVisible = true;
+
tgname = NameStr(trigrec->tgname);
appendStringInfo(&buf, "CREATE %sTRIGGER %s ",
OidIsValid(trigrec->tgconstraint) ? "CONSTRAINT " : "",
@@ -1007,23 +1039,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 +1076,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 +1099,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 +1143,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..daaeaf3a6b 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_string(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..a4c1f61bb0 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 as a single-line string',
+ proname => 'pg_get_triggerdef_string', provolatile => 's', prorettype => 'text',
+ proargtypes => 'oid', prosrc => 'pg_get_triggerdef_string' },
# 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)
Philip Alger <paalger0@gmail.com> writes:
I am attaching v3.
This patch will cause psql's \d to fail hard against any pre-v19
server. That's not acceptable. Note the comment at the top of
describe.c:
* Support for the various \d ("describe") commands. Note that the current
* expectation is that all functions in this file will succeed when working
* with servers of versions 9.2 and up. It's okay to omit irrelevant
* information for an old server, but not to fail outright. (But failing
* against a pre-9.2 server is allowed.)
You could get around that with a conditional on the server version,
as is done elsewhere in describe.c. But I kind of wonder why we
need this new function at all. Isn't "pg_get_triggerdef(oid, false)"
close enough?
An alternative suggestion is to keep "pg_get_triggerdef(oid, true)"
doing exactly what it does today and have the newly-introduced
function be the one that has new behavior. This might be a smarter
plan anyway to avoid side-swiping other clients that are expecting
the current behavior.
regards, tom lane
Hi Tom,
This patch will cause psql's \d to fail hard against any pre-v19
server. That's not acceptable. Note the comment at the top of
describe.c:* Support for the various \d ("describe") commands. Note that the current
* expectation is that all functions in this file will succeed when working
* with servers of versions 9.2 and up. It's okay to omit irrelevant
* information for an old server, but not to fail outright. (But failing
* against a pre-9.2 server is allowed.)
I didn't see that at the top of the file, and didn't think the new function
would break it all.
You could get around that with a conditional on the server version,
as is done elsewhere in describe.c. But I kind of wonder why we
need this new function at all. Isn't "pg_get_triggerdef(oid, false)"
close enough?
No. That works to print out the CREATE TRIGGER statement with the
schema.relation name. I am trying to make `pretty` print similar to what is
in pg_get_viewdef and pg_get_functiondef which formats the output.
An alternative suggestion is to keep "pg_get_triggerdef(oid, true)"
doing exactly what it does today and have the newly-introduced
function be the one that has new behavior. This might be a smarter
plan anyway to avoid side-swiping other clients that are expecting
the current behavior.
Thinking aloud, I would need to create a new function like
pg_get_triggerdef_worker and let it handle the formatting.
--
Best,
Phil Alger
EDB: https://www.enterprisedb.com