Horton

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

Jim Jones <jim.jones@uni-muenster.de>

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