Horton

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

Philip Alger <paalger0@gmail.com>

View thread
From: Philip Alger <paalger0@gmail.com>
To: Jim Jones <jim.jones@uni-muenster.de>
Cc: Andrew Dunstan <andrew@dunslane.net>, Cary Huang <cary.huang@highgo.ca>, jian he <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-10-16T19:04:56Z
Lists: pgsql-hackers

Attachments

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