Обсуждение: [PATCH] Add pretty formatting to pg_get_triggerdef

Поиск
Список
Период
Сортировка

[PATCH] Add pretty formatting to pg_get_triggerdef

От
Philip Alger
Дата:
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
Вложения

Re: [PATCH] Add pretty formatting to pg_get_triggerdef

От
Steven Niu
Дата:
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

Re: [PATCH] Add pretty formatting to pg_get_triggerdef

От
Chao Li
Дата:

> 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
secondparameter “false” should be updated to an int flag. 

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/