Hi,
I noticed that pg_get_triggerdef doesn't seem to use the schema qualified name of procedures/functions. Is it supposed to? Is there a way to get it to?
z1h=# \x auto
Expanded display is used automatically.
z1h=# select tg.tgname "name", nsp.nspname "schema", cls.relname table_name,
pg_get_triggerdef(tg.oid) full_definition, proc.proname proc_name,
nspp.nspname proc_schema, tg.tgenabled enabled
from pg_trigger tg
join pg_class cls on cls.oid = tg.tgrelid
join pg_namespace nsp on nsp.oid = cls.relnamespace
join pg_proc proc on proc.oid = tg.tgfoid
join pg_namespace nspp on nspp.oid = proc.pronamespace
where not tg.tgisinternal
order by schema, table_name, name;
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------
name | users_updated_at
schema | z1h
table_name | users
full_definition | CREATE TRIGGER users_updated_at BEFORE UPDATE ON z1h.users FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column()
proc_name | update_updated_at_column
proc_schema | public
enabled | O
This causes problems for tools like
migra that try to use the output of pg_get_triggerdef to recreate triggers that were defined in schemas other than public.
Thanks,
AJ