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

Hi Jim,

Appreciate the feedback!

> The function fails to look up triggers with quoted names
>

Not exactly. If you put "FOO" in the function pg_get_trigger_ddl('tbl',
'"FOO"') it will error because you don't need the double quotes. They are
already preserved. You just need the name, and pg_get_triggerdef works
similarly except with a plain OID.

postgres=# CREATE TRIGGER "🐘" BEFORE INSERT ON main_table FOR EACH
STATEMENT EXECUTE PROCEDURE trigger_func('modified_a');
postgres=# CREATE TRIGGER "FOO" BEFORE INSERT ON main_table FOR EACH
STATEMENT EXECUTE PROCEDURE trigger_func('modified_a');

postgres=# select tgname, oid from pg_trigger;
tgname | oid
--------------+-------
🐘 | 16397
FOO | 16498

(it does work if we omit the double quotes)
>

Right, the double quote does show up in the result. We aren't removing it.

postgres=# SELECT pg_get_trigger_ddl('main_table', '🐘');
pg_get_trigger_ddl

------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER "🐘" BEFORE INSERT ON public.main_table FOR EACH STATEMENT
EXECUTE FUNCTION trigger_func('modified_a');
(1 row)

pg_get_viewdef() sees it differently (opposite approach)
>

That's true, and it's pretty strict. However, pg_get_trigger_ddl seems more
intuitive since it can return the statement whether the trigger is quoted
or unquoted without the user thinking about adding quotes.

--
Best,
Phil Alger