Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement - Mailing list pgsql-hackers

From Philip Alger
Subject Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement
Date
Msg-id CAPXBC8+Ni1FTREUy7tn5EoJyUKkvrw7iAGxt4Y6Lu8_zX4PBcA@mail.gmail.com
Whole thread Raw
In response to Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement  (Jim Jones <jim.jones@uni-muenster.de>)
Responses Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement
List pgsql-hackers
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
Attachment

pgsql-hackers by date:

Previous
From: Alexander Lakhin
Date:
Subject: Re: Instability of phycodorus in pg_upgrade tests with JIT
Next
From: Tomas Vondra
Date:
Subject: Re: Optimizing ResouceOwner to speed up COPY