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?

From my understanding what you want to do is search the string you have entered through multiple models? Like lets say user enters 'hello' you want this hello to be searched in lets say models: cars,buses,bikes. Am I understanding your question right? — Jonathan, Oct 15 '12 at 19:02
@Jonathanq: No, every schema is identical, and I want to look for data in the same model, just across different schemas: so schema1.cars, schema2.cars, schema3.cars, etc. — Goro, Oct 15 '12 at 19:53

Erwin Brandstetter · Accepted Answer · 2023-02-06T13:58:41.913

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 for globally unique ids

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' <-- !!

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.

Sounds complicated. How well does django handle PostgreSQL inheritance? — Goro, Oct 15 '12 at 19:54
I am no expert with Django, but this shouldn't be complicated at all. In fact, if you have the same table in many schemas it greatly **simplifies** the `CREATE` script. To be clear: all the CREATE commands have to be executed once only. But that's clear anyway, right? What I forgot to add 8and added now): how to identify the source table. — Erwin Brandstetter, Oct 15 '12 at 20:10

score 1 · Answer 2 · answered Oct 15 '12 at 19:04

You will have to iterate over all namespaces. You can get a lot of this information from the pg_* system catalogs. In theory, you should be able to resolve the client -> schema mapping at request time without talking to the database so that the first SQL call you make is:

SET search_path = client1,global_schema;

Craig Ringer · Answer 3 · 2012-10-16T02:09:14.743

While I think Erwin's solution is probably preferable if you can re-structure your tables, an alternative that doesn't require any schema changes is to write a PL/PgSQL function that scans the tables using dynamic SQL based on the system catalog information.

Given:

CREATE SCHEMA a;
CREATE SCHEMA b;

CREATE TABLE a.testtab ( searchval text );
CREATE TABLE b.testtab (LIKE a.testtab);

INSERT INTO a.testtab(searchval) VALUES ('ham');
INSERT INTO b.testtab(searchval) VALUES ('eggs');

The following PL/PgSQL function searches all schemas containing tables named _tabname for values in _colname equal to _value and returns the first matching schema.

CREATE OR REPLACE FUNCTION find_schema_for_value(_tabname text, _colname text, _value text) RETURNS text AS $$
DECLARE
    cur_schema text;
    foundval integer;
BEGIN
    FOR cur_schema IN 
      SELECT nspname 
      FROM pg_class c 
      INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) 
      WHERE c.relname = _tabname AND c.relkind = 'r'
    LOOP
      EXECUTE 
        format('SELECT 1 FROM %I.%I WHERE %I = $1', 
            cur_schema, _tabname, _colname
        ) INTO foundval USING _value;
      IF foundval = 1 THEN
        RETURN cur_schema;
      END IF;
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

If there are are no matches then null is returned. If there are multiple matches the result will be one of them, but no guarantee is made about which one. Add an ORDER BY clause to the schema query if you want to return (say) the first in alphabetical order or something. The function is also trivially modified to return setof text and RETURN NEXT cur_schema if you want to return all the matches.

regress=# SELECT find_schema_for_value('testtab','searchval','ham');
 find_schema_for_value 
-----------------------
 a
(1 row)

regress=# SELECT find_schema_for_value('testtab','searchval','eggs');
 find_schema_for_value 
-----------------------
 b
(1 row)

regress=# SELECT find_schema_for_value('testtab','searchval','bones');
 find_schema_for_value 
-----------------------

(1 row)

By the way, you can re-use the table definitions without inheritance if you want, and you really should. Either use a common composite data type:

CREATE TYPE public.testtab AS ( searchval text );
CREATE TABLE a.testtab OF public.testtab;
CREATE TABLE b.testtab OF public.testtab;

in which case they share the same data type but not any data; or or via LIKE:

CREATE TABLE public.testtab ( searchval text );
CREATE TABLE a.testtab (LIKE public.testtab);
CREATE TABLE b.testtab (LIKE public.testtab);

in which case they're completely unconnected to each other after creation.

Find out which schema based on table values

3 Answers3

Linked