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
