sql - Find out which schema based on table values

Question

My database is separated into schemas based on clients (i.e.: each client has their own schema, with same data structure).

I also happen to have an external action that does not know which schema it should target. It comes from another part of the system that has no concepts of clients and does not know in which client's set it is operating. Before I process it, I have to find out which schema that request needs to target

To find the right schema, I have to find out which holds the record R with a particular unique ID (string)

From my understanding, the following

SET search_path TO schema1,schema2,schema3,...

will only look through the tables in schema1 (or the first schema that matches the table) and will not do a global search.

Is there a way for me to do a global search across all schemas, or am I just going to have to use a for loop and iterate through all of them, one at a time?

Answer 1

You could use inheritance for this. (Be sure to consider the limitations.)

Consider this little demo:

CREATE SCHEMA master;  -- no access of others ..

CREATE SEQUENCE master.myseq;  -- global sequence to have globally unique id
CREATE table master.tbl (
  id int primary key DEFAULT nextval('master.myseq')
, foo text);

CREATE SCHEMA x;
CREATE table x.tbl() INHERITS (master.tbl);
INSERT INTO  x.tbl(foo) VALUES ('x');

CREATE SCHEMA y;
CREATE table y.tbl() INHERITS (master.tbl);
INSERT INTO  y.tbl(foo) VALUES ('y');


SELECT * FROM x.tbl;  -- returns 'x'
SELECT * FROM y.tbl;  -- returns 'y'
SELECT * FROM master.tbl;  -- returns 'x' and 'y' <-- !!

-- clean it all up:
-- DROP SCHEMA x, y, master CASCADE;

Now, to actually identify the table a particular row lives in, use the tableoid:

SELECT *, tableoid::regclass AS table_name
FROM   master.tbl
WHERE  id = 2;

Result:

id | foo | table_name
---+-----+-----------
2  | y   | y.tbl

You can derive the source schema from the tableoid, best by querying the system catalogs with the tableoid directly. (The displayed name depends on the setting of search_path.)

SELECT n.nspname 
FROM   master.tbl   t
JOIN   pg_class     c ON c.oid = t.tableoid
JOIN   pg_namespace n ON c.relnamespace = n.oid
WHERE  t.id = 2;

This is also much faster than looping through many separate tables.