[PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Started by Philip Alger5 months ago49 messages
Jump to latest
#1Philip Alger
paalger0@gmail.com

Hello,

I am submitting patch as a part of a larger Retail DDL functions project
described by Andrew Dunstan here:
/messages/by-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9@dunslane.net

This patch creates a function pg_get_trigger_ddl, designed to retrieve the
full DDL statement for a trigger. Users can obtain 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 to find 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.

--
Best,
Phil Alger

Attachments:

0001-Add-pg_get_trigger_ddl-function.patchapplication/octet-stream; name=0001-Add-pg_get_trigger_ddl-function.patchDownload+175-1
#2jian he
jian.universality@gmail.com
In reply to: Philip Alger (#1)
Re: [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: /messages/by-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9@dunslane.net

This patch creates a function pg_get_trigger_ddl, designed to retrieve the full DDL statement for a trigger. Users can obtain 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 to find 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?

#3Philip Alger
paalger0@gmail.com
In reply to: jian he (#2)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

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 exists for the table. I've
included a test for that as well. It should return a clearer error if the
relation does not exist.

--
Best,
Phil Alger

Attachments:

v2-0001-Add-pg_get_trigger_ddl-function.patchapplication/octet-stream; name=v2-0001-Add-pg_get_trigger_ddl-function.patchDownload+184-1
#4jian he
jian.universality@gmail.com
In reply to: Philip Alger (#3)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

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 exists for 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)

#5Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: jian he (#4)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

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

#6Philip Alger
paalger0@gmail.com
In reply to: jian he (#4)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

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)

But isn't that an edge case? Would a user really pass in an arbitrary
number like -1? That seems counterintuitive.

--
Best, Phil Alger

#7Cary Huang
cary.huang@highgo.ca
In reply to: Philip Alger (#6)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement
+{ 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

#8Philip Alger
paalger0@gmail.com
In reply to: Cary Huang (#7)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi,

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.

Thanks for pointing that out in the documentation.

I think the regclass type actually makes it easier to use because you can
input a name or OID, but you're right in that it is essentially
'table_name'::regclass::oid. However, isn't the point of the regclass type
to make it easier to do table lookups? In that case, using a name seems
easier.

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.

True, but you have to look for the trigger OID. If you have more than one
table using the same trigger name, then you have to figure that out as
well. Using names over OIDs seems more intuitive and less error prone than
having to look up all the OIDs in my opinion. Also, having the added option
of using an OID as well shouldn't be frowned upon since that's what it's
using under the hood with regclass, but I understand what you're saying
about pg_get_triggerdef(OID) doing the same thing with the OID only.

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.)"

True, Cary. Appreciate calling that out. I can fix that in the
documentation as well.

--
Best, Phil Alger

#9Philip Alger
paalger0@gmail.com
In reply to: Philip Alger (#8)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Apologies, I forgot to add a new version of the patch with the
documentation change.

This is my first time doing this.

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.

Thanks for pointing that out in the documentation.

I think the regclass type actually makes it easier to use because you can
input a name or OID, but you're right in that it is essentially
'table_name'::regclass::oid. However, isn't the point of the regclass type
to make it easier to do table lookups? In that case, using a name seems
easier.

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.

True, but you have to look for the trigger OID. If you have more than one
table using the same trigger name, then you have to figure that out as
well. Using names over OIDs seems more intuitive and less error prone than
having to look up all the OIDs in my opinion. Also, having the added option
of using an OID as well shouldn't be frowned upon since that's what it's
using under the hood with regclass, but I understand what you're saying
about pg_get_triggerdef(OID) doing the same thing with the OID only.

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.)"

True, Cary. Appreciate calling that out. I can fix that in the
documentation as well.

--
Best, Phil Alger

Attachments:

v3-0001-Add-pg_get_trigger_ddl-function.patchapplication/octet-stream; name=v3-0001-Add-pg_get_trigger_ddl-function.patchDownload+181-1
#10Andrew Dunstan
andrew@dunslane.net
In reply to: Philip Alger (#9)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

On 2025-10-14 Tu 5:29 PM, Philip Alger wrote:

Apologies, I forgot to add a new version of the patch with the
documentation change.

This is my first time doing this.

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.

Thanks for pointing that out in the documentation.

I think the regclass type actually makes it easier to use because
you can input a name or OID, but you're right in that it is
essentially 'table_name'::regclass::oid. However, isn't the point
of the regclass type to make it easier to do table lookups? In
that case, using a name seems easier.

I think you should change the documentation. It seems better to use the
regfoo types where available to save a lot a code duplication.

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.

True, but you have to look for the trigger OID. If you have more
than one table using the same trigger name, then you have to
figure that out as well. Using names over OIDs seems more
intuitive and less error prone than having to look up all the OIDs
in my opinion. Also, having the added option of using an OID as
well shouldn't be frowned upon since that's what it's using under
the hood with regclass, but I understand what you're saying about
pg_get_triggerdef(OID) doing the same thing with the OID only.

Yes, what this function buys us anything is that you don't need to get
the trigger OID.

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.)"

True, Cary. Appreciate calling that out. I can fix that in the
documentation as well.

by "DDL statement" we mean a statement that would create the object as
it exists now if it were not already present, not the original creation
statement. I don't think we need to state that all over the place.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

#11Philip Alger
paalger0@gmail.com
In reply to: Andrew Dunstan (#10)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Thank you Andrew.

I think you should change the documentation.

Changed.

I've updated v4, attached here. One thing I noted while testing was that
pg_get_triggerdef does not put the statement terminator (;) at the end of
the printed statement.

pg_get_triggerdef

----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH
ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a')
(1 row)

I accounted for that in v4.

SELECT pg_get_trigger_ddl('main_table', 'modified_a');

pg_get_trigger_ddl

-----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH
ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a');
(1 row)

--
Best,
Phil Alger

Attachments:

v4-0001-Add-pg_get_trigger_ddl-function.patchapplication/octet-stream; name=v4-0001-Add-pg_get_trigger_ddl-function.patchDownload+185-1
#12jian he
jian.universality@gmail.com
In reply to: Philip Alger (#6)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

On Tue, Oct 14, 2025 at 9:14 PM Philip Alger <paalger0@gmail.com> 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)

But isn't that an edge case? Would a user really pass in an arbitrary number like -1? That seems counterintuitive.

hi.

I complained the same issue for pg_basetype at
/messages/by-id/3759807.1711658868@sss.pgh.pa.us

maybe we can return NULL for
select pg_get_trigger_ddl(-1, 'h');

#13Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#11)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Phil,

Thanks for the patch.

On 10/15/25 23:25, Philip Alger wrote:

I've updated v4, attached here.

The function fails to look up triggers with quoted names

db=# CREATE TABLE t (c int);
CREATE TABLE

db=# CREATE FUNCTION trgf()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END; $$;
CREATE FUNCTION

db=# CREATE TRIGGER "Foo"
BEFORE INSERT ON t
FOR EACH STATEMENT EXECUTE PROCEDURE trgf();
CREATE TRIGGER

db=# SELECT pg_get_trigger_ddl('t','"Foo"');
ERROR: trigger ""Foo"" for table "t" does not exist

The same applies for unicode trigger names:

db=# CREATE TRIGGER "🐘"
BEFORE INSERT ON t
FOR EACH STATEMENT EXECUTE PROCEDURE trgf();
CREATE TRIGGER

db=# SELECT pg_get_trigger_ddl('t','"🐘"');
ERROR: trigger ""🐘"" for table "t" does not exist

db=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |
Triggers:
"Foo" BEFORE INSERT ON t FOR EACH STATEMENT EXECUTE FUNCTION trgf()
"🐘" BEFORE INSERT ON t FOR EACH STATEMENT EXECUTE FUNCTION trgf()

(it does work if we omit the double quotes)

postgres=# SELECT pg_get_trigger_ddl('t','Foo');
pg_get_trigger_ddl

--------------------------------------------------------------------------------------------
CREATE TRIGGER "Foo" BEFORE INSERT ON public.t FOR EACH STATEMENT
EXECUTE FUNCTION trgf();
(1 row)

postgres=# SELECT pg_get_trigger_ddl('t','🐘');
pg_get_trigger_ddl

-------------------------------------------------------------------------------------------
CREATE TRIGGER "🐘" BEFORE INSERT ON public.t FOR EACH STATEMENT
EXECUTE FUNCTION trgf();
(1 row)

I don't think it's the expected behaviour. For instance,
pg_get_viewdef() sees it differently (opposite approach):

postgres=# CREATE TEMPORARY VIEW "MyView" AS SELECT 42;
CREATE VIEW

postgres=# SELECT pg_get_viewdef('"MyView"');
pg_get_viewdef
---------------------------
SELECT 42 AS "?column?";
(1 row)

postgres=# SELECT pg_get_viewdef('MyView');
ERROR: relation "myview" does not exist

Best, Jim

#14Philip Alger
paalger0@gmail.com
In reply to: jian he (#12)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Jian,

maybe we can return NULL for
select pg_get_trigger_ddl(-1, 'h');

Yes, I had the same idea last night. Running PG_RETURN_NULL would also be
similar to how other functions handle it.

Thanks, and I will make the change.

--
Best, Phil Alger

#15Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#13)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Jim,

Appreciate the feedback!

The function fails to look up triggers with quoted names

Not exactly. If you put "FOO" in the function pg_get_trigger_ddl('tbl',
'"FOO"') it will error because you don't need the double quotes. They are
already preserved. You just need the name, and pg_get_triggerdef works
similarly except with a plain OID.

postgres=# CREATE TRIGGER "🐘" BEFORE INSERT ON main_table FOR EACH
STATEMENT EXECUTE PROCEDURE trigger_func('modified_a');
postgres=# CREATE TRIGGER "FOO" BEFORE INSERT ON main_table FOR EACH
STATEMENT EXECUTE PROCEDURE trigger_func('modified_a');

postgres=# select tgname, oid from pg_trigger;
tgname | oid
--------------+-------
🐘 | 16397
FOO | 16498

(it does work if we omit the double quotes)

Right, the double quote does show up in the result. We aren't removing it.

postgres=# SELECT pg_get_trigger_ddl('main_table', '🐘');
pg_get_trigger_ddl

------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "🐘" BEFORE INSERT ON public.main_table FOR EACH STATEMENT
EXECUTE FUNCTION trigger_func('modified_a');
(1 row)

pg_get_viewdef() sees it differently (opposite approach)

That's true, and it's pretty strict. However, pg_get_trigger_ddl seems more
intuitive since it can return the statement whether the trigger is quoted
or unquoted without the user thinking about adding quotes.

--
Best,
Phil Alger

#16Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#13)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Jim,

Just to add to this:

I don't think it's the expected behaviour. For instance,
pg_get_viewdef() sees it differently (opposite approach):

postgres=# SELECT pg_get_viewdef('"MyView"');
pg_get_viewdef
---------------------------
SELECT 42 AS "?column?";
(1 row)

I saw from the docs that pg_get_viewdef('name') is deprecated and instead
users should use an OID:
https://www.postgresql.org/docs/18/functions-info.html#FUNCTIONS-INFO-CATALOG

--
Best, Phil Alger

#17Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#15)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

On 10/16/25 16:20, Philip Alger wrote:

pg_get_viewdef() sees it differently (opposite approach)

That's true, and it's pretty strict. However, pg_get_trigger_ddl seems
more intuitive since it can return the statement whether the trigger is
quoted or unquoted without the user thinking about adding quotes.

I can see how it can be more practical to not care about double quotes
when using pg_get_trigger_ddl(), but IMHO consistency and predictability
are more important in this particular case. If we do this, users would
need to know where to keep or remove the double quotes when using
functions to describe catalog objects. Another argument **for** my case
is the following example:

postgres=# CREATE SCHEMA "S";
CREATE SCHEMA

postgres=# CREATE TABLE "S"."T" (c int);
CREATE TABLE

postgres=# CREATE FUNCTION "S".trgf()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER trg BEFORE INSERT ON "S"."T"
FOR EACH STATEMENT EXECUTE PROCEDURE "S".trgf();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','trg');
pg_get_trigger_ddl

---------------------------------------------------------------------------------------------
CREATE TRIGGER trg BEFORE INSERT ON "S"."T" FOR EACH STATEMENT EXECUTE
FUNCTION "S".trgf();
(1 row)

postgres=# SELECT pg_get_trigger_ddl('S.T','trg');
ERROR: relation "s.t" does not exist
LINE 1: SELECT pg_get_trigger_ddl('S.T','trg');

The table parameter expects the double quotes, so it would be a hard
sell to make the trigger name parameter **not accept them** either.

In that light, I tend to think that the approach of pg_get_viewdef()
would be the best way to go, but let's see what the other reviewers have
to say about it.

Best, Jim

#18Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#17)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Jim,

I can see how it can be more practical to not care about double quotes
when using pg_get_trigger_ddl(), but IMHO consistency and predictability
are more important in this particular case. If we do this, users would
need to know where to keep or remove the double quotes when using
functions to describe catalog objects.

I see what you mean.

I refactored the code in v5 attached and it should now be strict and use
double quotes for those scenarios. Additionally, it takes care of the -1
OID issue.

The output of your examples using double quotes:

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','trg');
pg_get_trigger_ddl

---------------------------------------------------------------------------------------------
CREATE TRIGGER trg BEFORE INSERT ON "S"."T" FOR EACH STATEMENT EXECUTE
FUNCTION "S".trgf();
(1 row)

postgres=# CREATE TRIGGER "TRG2" BEFORE INSERT ON "S"."T"

FOR EACH STATEMENT EXECUTE
PROCEDURE "S".trgf();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','TRG2');
2025-10-16 14:03:38.910 CDT [81664] ERROR: trigger "trg2" for table "T"
does not exist
2025-10-16 14:03:38.910 CDT [81664] STATEMENT: SELECT
pg_get_trigger_ddl('"S"."T"','TRG2');
ERROR: trigger "trg2" for table "T" does not exist

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','"TRG2"');
pg_get_trigger_ddl

------------------------------------------------------------------------------------------------
CREATE TRIGGER "TRG2" BEFORE INSERT ON "S"."T" FOR EACH STATEMENT EXECUTE
FUNCTION "S".trgf();
(1 row)

and for -1

postgres=# SELECT pg_get_trigger_ddl(-1,'trg');
pg_get_trigger_ddl
--------------------

(1 row)

--
Best,
Phil Alger

Attachments:

v5-0001-Add-pg_get_trigger_ddl-function.patchapplication/octet-stream; name=v5-0001-Add-pg_get_trigger_ddl-function.patchDownload+296-1
#19Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#18)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Phil

On 10/16/25 21:04, Philip Alger wrote:

I refactored the code in v5 attached and it should now be strict and use
double quotes for those scenarios. Additionally, it takes care of the -1
OID issue.

Nice!

v5 now parses the double quotes correctly:

postgres=# CREATE SCHEMA "S";
CREATE SCHEMA

postgres=# CREATE TABLE "S"."T" (c int);
CREATE TABLE

postgres=# CREATE FUNCTION "S"."TriggerFunc"()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER "MyTrigger" BEFORE INSERT ON "S"."T"
FOR EACH STATEMENT EXECUTE PROCEDURE "S"."TriggerFunc"();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','"MyTrigger"');
pg_get_trigger_ddl

--------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "MyTrigger" BEFORE INSERT ON "S"."T" FOR EACH STATEMENT
EXECUTE FUNCTION "S"."TriggerFunc"();
(1 row)

... making non-quoted object names case insensitive:

postgres=# CREATE TABLE t (c int);
CREATE TABLE

postgres=# CREATE FUNCTION trgfunc()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER mytrigger BEFORE INSERT ON t
FOR EACH STATEMENT EXECUTE PROCEDURE trgfunc();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('T','MYTRIGGER');
pg_get_trigger_ddl

---------------------------------------------------------------------------------------------------
CREATE TRIGGER mytrigger BEFORE INSERT ON public.t FOR EACH STATEMENT
EXECUTE FUNCTION trgfunc();
(1 row)

-1 and NULL for the table name now return NULL.

The tests were also updated accordingly.

I am now wondering if introducing these new set of parameters to
pg_get_triggerdef() would be a better solution that creating a new
function. Like pg_get_indexdef():

{ oid => '2507', descr => 'index description (full create statement or
single expression) with pretty-print option', proname =>
'pg_get_indexdef', provolatile => 's', prorettype => 'text', proargtypes
=> 'oid int4 bool', prosrc => 'pg_get_indexdef_ext' },

...

{ oid => '1643', descr => 'index description', proname =>
'pg_get_indexdef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_indexdef' },

Doing so we keep it consistent with the other pg_get*def functions. What
do you think?

Thanks!

Best, Jim

#20Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#19)
Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement

Hi Jim,

I am now wondering if introducing these new set of parameters to

pg_get_triggerdef() would be a better solution that creating a new
function.

Doing so we keep it consistent with the other pg_get*def functions. What
do you think?

The rationale behind it is here:
/messages/by-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9@dunslane.net

So, I am new to PG development, but I am hesitant to modify the existing
`pg_get_triggerdef` or its parameters. My concern is that users may
currently rely on its existing functionality and parameter structure, and
altering it could introduce breaking changes. I think the naming
`pg_get_trigger_ddl` is actually better than `triggerdef` because all the
current `pg_get*def` implementations accept OIDs. To my knowledge, the only
one that accepted an OIDs or a name is `pg_get_viewdef`, but the name
variant is now deprecated.

--
Best,
Phil Alger

#21Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#20)
#22Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#21)
#23Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#22)
#24Marcos Pegoraro
marcos@f10.com.br
In reply to: Jim Jones (#23)
#25Philip Alger
paalger0@gmail.com
In reply to: Marcos Pegoraro (#24)
#26Marcos Pegoraro
marcos@f10.com.br
In reply to: Philip Alger (#25)
#27Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#23)
#28Philip Alger
paalger0@gmail.com
In reply to: Marcos Pegoraro (#26)
#29Philip Alger
paalger0@gmail.com
In reply to: Philip Alger (#27)
#30Tom Lane
tgl@sss.pgh.pa.us
In reply to: Philip Alger (#29)
#31Philip Alger
paalger0@gmail.com
In reply to: Tom Lane (#30)
#32Jim Jones
jim.jones@uni-muenster.de
In reply to: Tom Lane (#30)
#33jian he
jian.universality@gmail.com
In reply to: Philip Alger (#27)
#34Philip Alger
paalger0@gmail.com
In reply to: jian he (#33)
#35Philip Alger
paalger0@gmail.com
In reply to: Philip Alger (#34)
#36Philip Alger
paalger0@gmail.com
In reply to: Philip Alger (#35)
#37Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#36)
#38Chao Li
li.evan.chao@gmail.com
In reply to: Philip Alger (#36)
#39jian he
jian.universality@gmail.com
In reply to: Jim Jones (#37)
#40Jim Jones
jim.jones@uni-muenster.de
In reply to: jian he (#39)
#41Philip Alger
paalger0@gmail.com
In reply to: Jim Jones (#40)
#42Jim Jones
jim.jones@uni-muenster.de
In reply to: Philip Alger (#41)
#43Josef Šimánek
retro@ballgag.cz
In reply to: Philip Alger (#36)
#44Josef Šimánek
retro@ballgag.cz
In reply to: Chao Li (#38)
#45Philip Alger
paalger0@gmail.com
In reply to: Josef Šimánek (#43)
#46Philip Alger
paalger0@gmail.com
In reply to: Josef Šimánek (#44)
#47Josef Šimánek
josef.simanek@gmail.com
In reply to: Philip Alger (#45)
#48Josef Šimánek
josef.simanek@gmail.com
In reply to: Philip Alger (#46)
#49Philip Alger
paalger0@gmail.com
In reply to: Josef Šimánek (#48)