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

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
>

I tested the patch on the latest master. The patch applied cleanly,
and re-initialized the database cluster as required. I tested the
functionality with different types of triggers. Basic triggers worked
as per the expectations. Triggers with multiple events like INSERT OR
UPDATE were handled correctly, and statement-level triggers were also
represented properly. I also verified that when multiple triggers
exist on the same table, each one is identified and returned correctly
without any confusion. Additionally, I tested constraint triggers too
and the overall structure and order of the generated statement looked
complete and correct. Also I tested both schema-qualified and
non-qualified table names, in both cases the function behaved
consistently and always returned the table name in schema-qualified
form that helps avoid ambiguity. Compared to the existing
pg_get_triggerdef() function, this new function is easier to use since
it directly accepts the table name and trigger name instead of OID
lookup from pg_trigger which seems good to me too. It also aligns well
with other pg_get helper functions. And another notable improvement is
that the output includes a terminating semicolon making it easier to
reuse directly. I also tested some edge cases like when a non-existent
trigger or an incorrect table is provided, the function returns
appropriate error messages which also seems good. 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. Overall, the function is useful and improves usability. I
did not find any major issues apart from handling quoted trigger
names. Otherwise the patch LGTM.

Regards,
Soumya