Обсуждение: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

On Tue, Oct 14, 2025 at 9:59 AM Philip Alger <paalger0@gmail.com> wrote:
>
> Hello,
>
> I am submitting patch as a part of a larger Retail DDL functions project described by Andrew Dunstan here:
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
>
> This patch creates a function pg_get_trigger_ddl, designed to retrieve the full DDL statement for a trigger. Users
canobtain the DDL by providing the table and trigger names, like the following: 
>
>     SELECT pg_get_trigger_ddl('my_table_name', 'my_trigger_name');
>
> While pg_get_triggerdef currently provides a similar SQL statement, it requires the trigger's OID, making it less
convenient.This function simplifies this by allowing direct input of the table and trigger names, eliminating the need
tofind the OID beforehand. I opted not to include the "pretty" formatting capability that pg_get_triggerdef offers. 
>
> This patch includes documentation, comments, and regression tests, all of which have run successfully.
>

I just did a quick test.

src1=# SELECT pg_get_trigger_ddl(2, 'foo_trigger');
ERROR:  trigger "foo_trigger" for table "(null)" does not exist
src1=# SELECT pg_get_trigger_ddl(0, 'foo_trigger');
ERROR:  trigger "foo_trigger" for table "(null)" does not exist

this error message is use facing, is the above error message what we expected?

On Tue, Oct 14, 2025 at 12:03 PM Philip Alger <paalger0@gmail.com> wrote:
>
>
>
> On Mon, Oct 13, 2025 at 9:28 PM jian he <jian.universality@gmail.com> wrote:
>>
>>
>> I just did a quick test.
>>
>> src1=# SELECT pg_get_trigger_ddl(2, 'foo_trigger');
>> ERROR:  trigger "foo_trigger" for table "(null)" does not exist
>> src1=# SELECT pg_get_trigger_ddl(0, 'foo_trigger');
>> ERROR:  trigger "foo_trigger" for table "(null)" does not exist
>>
>> this error message is use facing, is the above error message what we expected?
>
>
> Thank you for checking that. Short answer: no.
>
> Please see v2. The latest version should take care of the (null) relation issue now, since it is checking if the OID
existsfor the table. I've included a test for that as well. It should return a clearer error if the relation does not
exist.
>

select  pg_get_trigger_ddl(-1, 'h');
ERROR:  relation with OID 4294967295 does not exist

this error obviously is not good.
we can follow the approach used by pg_get_viewdef(oid)

On 2025-Oct-14, jian he wrote:

> select  pg_get_trigger_ddl(-1, 'h');
> ERROR:  relation with OID 4294967295 does not exist
> 
> this error obviously is not good.
> we can follow the approach used by pg_get_viewdef(oid)

Hmm.  For pg_get_viewdef() we have two functions:

{ oid => '1640', descr => 'select statement of a view',
  proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r',
  prorettype => 'text', proargtypes => 'text',
  prosrc => 'pg_get_viewdef_name' },

{ oid => '1641', descr => 'select statement of a view',
  proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r',
  prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_viewdef' },

one of which takes a 'name' reference the table, and the other takes
OID.  I suspect this arrangement predates the 'regclass' business ... 

   git show 52200befd0^:src/backend/utils/adt/ruleutils.c

yep, it does.  I think we wouldn't do it this way nowadays.  I think the
choice to implement pg_get_trigger_ddl(regclass) is a good one.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Every machine is a smoke machine if you operate it wrong enough."
https://twitter.com/libseybieda/status/1541673325781196801

+{ oid => '9569', descr => 'get CREATE statement for a trigger',
+  proname => 'pg_get_trigger_ddl', proisstrict => 't', prorettype => 'text',
+  proargtypes => 'regclass name', prosrc => 'pg_get_trigger_ddl' },

your documentation and the function's comment specifically say that the
function take a trigger name and a table name, so it should not use
regclass type, which allows OID as input as well. 

There is already a family of pg_get_[xxx]def functions available in
PostgreSQL. pg_get_triggerdef() being one of them and it already can take
OID as input and output the same text, so regclass type is not necessary. 

Consistency is important in PostgreSQL, so instead of creating a new
function pg_get_trigger_ddl(), I think it is better to just overload the
original pg_get_triggerdef() function by adding another version of it that
takes trigger and table name instead and you should keep the pretty
boolean argument as well for consistency.

Personally, I think the existing pg_triggerdef() functions shall be sufficient,
as it is relatively easy to look up a trigger OID, but perhaps in some
scenarios using trigger name + table name may be more convenient. 

Again for consistency, in addition to triggers, there are other functions like
pg_get_viewdef, and pg_get_indexdef that take OID and return the creating
commands for a view and index, these can also have the same variant of
taking view/index name and table name just like triggers.

+/* ----------
+ * pg_get_trigger_ddl - Get the DDL statement for a trigger
+ *
+ * This function retrieves the DDL statement for a specified trigger given a
+ * table name and trigger name. It uses the pg_get_triggerdef_worker function
+ * to perform the actual retrieval of the DDL statement. This function allows
+ * users to obtain the DDL definition of a trigger in a convenient manner using
+ * the trigger's name and the table it belongs to, rather than having to
+ * look up the trigger OID first to obtain the definition.
+ * ----------
+ */

The term "DDL statement" may be a little misleading here, it does not return
the actual DDL statements executed to create the trigger. The documentation for
"pg_get_triggerdef" calls this statement as follows :

"the creating command for a trigger. (This is a decompiled reconstruction,
not the original text of the command.)"


Cary