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