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.
