I have written function that I registered in a trigger that ensure referential consistency in non-normalized data (like we store agency_id + branch_id and I ensure that a referenced branch is inside a reference agency).
The function has some selects:
SELECT b.agency_id INTO agency_id FROM branch AS b WHERE b.id = NEW.branch_id; IF NEW.agency_id <> agency_id THEN RAISE EXCEPTION 'privilege.agency_id (%) mismatches privilege.branch_id => agency.id (%)', NEW.agency_id, agency_id; END IF;
All worked fine for several years until I run
pg_dump. On import by
psql ... -f dump/dict-local.sql I see errors:
ERROR: 42P01: relation "branch" does not exist LINE 1: SELECT b.agency_id FROM branch AS b WHERE b.i... ^ QUERY: SELECT b.agency_id FROM branch AS b WHERE b.id = NEW.branch_id CONTEXT: PL/pgSQL function SCHEMA.privilege_orgunit_ck() line 8 at SQL statement
After I added schema prefix:
SCHEMA.branch import started to work. I don't like to add schema prefix to tables referenced in the function. What can I do to avoid the error?
Is it related to
search_path? I seee in the very beginning of
SELECT pg_catalog.set_config('search_path', '', false);
UPDATE There is TG_TABLE_SCHEMA mentioned in the docs. I'm not sure how to use it, using it as prefix
TG_TABLE_SCHEMA.branch produces the same error:
psql:dump/dict-local.sql:4368: ERROR: 42P01: relation "tg_table_schema.branch" does not exist LINE 1: SELECT b.agency_id FROM TG_TABLE_SCHEMA.branc...
If I need to rewrite select into:
EXECUTE format('SELECT ... INTO ... FROM %I.branch', TG_TABLE_SCHEMA);
it looks awful...