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

On Tue, May 5, 2026 at 4:00 PM Soumya S Murali
<soumyamurali(dot)work(at)gmail(dot)com> wrote:
>
> Hi all,
>
> On Tue, May 5, 2026 at 10:44 AM Philip Alger <paalger0(at)gmail(dot)com> wrote:
> >
> >
> >>
> >>>> doc said trigger name can not be schema-qualified,
> >>>> we can not do:
> >>>> CREATE TRIGGER public.modified_a BEFORE UPDATE OF a ON main_table
> >>>> FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
> >>>
> >>>
> >>>>
> >>>> + nameList = textToQualifiedNameList(trgName);
> >>>>
> >>
> >> I am wondering if adding an error message if someone inserted a schema name would be advantageous?
> >
> >
> > It might be advantageous to show a `trigger name cannot be schema qualified` error to the user. Therefore, I added the check and the tests on v8 attached.
> >
> > postgres=# SELECT pg_get_trigger_ddl('main_table', 'public.modified_a');
> > ERROR: trigger name cannot be schema qualified
> >
>
>
> But one issue I
> found is with triggers that have quoted names:
>
> postgres=# CREATE TRIGGER "Weird-Trigger!" BEFORE INSERT ON test_table
> FOR EACH ROW EXECUTE FUNCTION test_trigger_func();
> CREATE TRIGGER
> postgres=# SELECT pg_get_trigger_ddl('test_table'::regclass, 'Weird-Trigger!');
> ERROR: trigger "weird-trigger!" for table "test_table" does not exist
> postgres=#
>
> When calling pg_get_trigger_ddl('test_table', 'Weird-Trigger!'), the
> function fails to find the trigger. It only works if the name is
> passed with quotes inside the string like
> pg_get_trigger_ddl('test_table'::regclass, '"Weird-Trigger!"'):
>
> postgres=# SELECT pg_get_trigger_ddl('test_table'::regclass,
> '"Weird-Trigger!"');
> pg_get_trigger_ddl
> -----------------------------------------------------------------------------------------------------------------------
> CREATE TRIGGER "Weird-Trigger!" BEFORE INSERT ON public.test_table
> FOR EACH ROW EXECUTE FUNCTION test_trigger_func();
> (1 row)
>
> This suggests that the function is not treating the trigger name as
> exact text and is instead converting it to lowercase internally. It
> would be better if the function matched trigger names exactly as
> given. Alternatively, the expected quoting behavior could be clearly
> documented.

In support of this, I have tried testing after making a small change
in the function pg_get_trigger_ddl() in ruleutils.c:

Instead of this:
/*Parse the trigger name to handle quoted identifiers */
nameList = textToQualifiedNameList(trgName);
if (list_length(nameList) != 1)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("trigger name cannot be schema qualified")));
DeconstructQualifiedName(nameList, &schemaName, &objName);

Do: objName = text_to_cstring(trgName); to treat the trigger name
argument as exact text instead of parsing it as an identifier. With
this change, quoted trigger names work as expected without requiring
extra quoting:

postgres=# SELECT pg_get_trigger_ddl('test_table'::regclass, 'Weird-Trigger!');
pg_get_trigger_ddl
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "Weird-Trigger!" BEFORE INSERT ON public.test_table
FOR EACH ROW EXECUTE FUNCTION test_trigger_func();
(1 row)

I also verified that this change does not affect existing behavior for
normal triggers, multi-event triggers, statement-level triggers, or
constraint triggers. If preferred, this can also be handled as an
improvement to the existing patch. Looking forward to more feedback on
this.

Regards,
Soumya