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

Hi Phil

On 10/16/25 21:04, Philip Alger wrote:
> 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.

Nice!

v5 now parses the double quotes correctly:

postgres=# CREATE SCHEMA "S";
CREATE SCHEMA

postgres=# CREATE TABLE "S"."T" (c int);
CREATE TABLE

postgres=# CREATE FUNCTION "S"."TriggerFunc"()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER "MyTrigger" BEFORE INSERT ON "S"."T"
FOR EACH STATEMENT EXECUTE PROCEDURE "S"."TriggerFunc"();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('"S"."T"','"MyTrigger"');
                                              pg_get_trigger_ddl

--------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER "MyTrigger" BEFORE INSERT ON "S"."T" FOR EACH STATEMENT
EXECUTE FUNCTION "S"."TriggerFunc"();
(1 row)

... making non-quoted object names case insensitive:

postgres=# CREATE TABLE t (c int);
CREATE TABLE

postgres=# CREATE FUNCTION trgfunc()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RETURN NULL;
END; $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER mytrigger BEFORE INSERT ON t
FOR EACH STATEMENT EXECUTE PROCEDURE trgfunc();
CREATE TRIGGER

postgres=# SELECT pg_get_trigger_ddl('T','MYTRIGGER');
                                        pg_get_trigger_ddl

---------------------------------------------------------------------------------------------------
 CREATE TRIGGER mytrigger BEFORE INSERT ON public.t FOR EACH STATEMENT
EXECUTE FUNCTION trgfunc();
(1 row)


-1 and NULL for the table name now return NULL.

The tests were also updated accordingly.

I am now wondering if introducing these new set of parameters to
pg_get_triggerdef() would be a better solution that creating a new
function. Like pg_get_indexdef():

{ oid => '2507', descr => 'index description (full create statement or
single expression) with pretty-print option', proname =>
'pg_get_indexdef', provolatile => 's', prorettype => 'text', proargtypes
=> 'oid int4 bool', prosrc => 'pg_get_indexdef_ext' },

...

{ oid => '1643', descr => 'index description', proname =>
'pg_get_indexdef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_indexdef' },

Doing so we keep it consistent with the other pg_get*def functions. What
do you think?

Thanks!

Best, Jim