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