Обсуждение: [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/

Re: [PATCH] Add pretty formatting to pg_get_triggerdef

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