6

I have multiple schemas that I need to query across. The schemas are not known in advance, for example:

  • U111
  • U222
  • U333

I need to select across these three schemas from a specific table in each of these schemas. Can I put this query across these three schemas? If so, how can I query across them? Remember, I will not know the schema names ahead of time, so they will need to be built dynamically.

  • You could write down a function that will query the pg_namespace table (which contains all the schemas in the DB) and then run the query against every table in the schema if exists. – Fabrizio Mazzoni Mar 22 '14 at 16:14
  • Fabrizio, thank you for your respone. Is there is a way to accomplish this in a view instead of a function? – larkmullins Mar 22 '14 at 16:58
  • Is the table in all the schemas exactly the same? – Fabrizio Mazzoni Mar 22 '14 at 17:10
  • Yes, all schemas will have the exact same tables with each table structured exactly the same. – larkmullins Mar 22 '14 at 17:12
  • 1
    Use dynamic SQL - PL/PgSQL with EXECUTE. – Craig Ringer Mar 23 '14 at 11:15
9

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:

SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
...

The schema search path search_path in Postgres works much like the search path a file system. Related:

If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client or use a plpgsql function with dynamic SQL and EXECUTE. That's what I would do. Plain SQL does not allow parametrized identifiers (schema, table, column, ...).

CREATE OR REPLACE FUNCTION foo(_schemas text[])
  RETURNS TABLE (bar int, baz text) AS  -- matching return type
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT string_agg(format('SELECT bar, baz FROM %I.foo', sch)  -- your query here
                          , E'\nUNION ALL\n')
   FROM   unnest(_schemas) sch
   );
END
$func$  LANGUAGE plpgsql;

Builds and executes a query of the following form dynamically:

SELECT bar, baz FROM u111.foo
UNION ALL
SELECT bar, baz FROM u222.foo
UNION ALL
SELECT bar, baz FROM u333.foo;

Schema names are escaped as identifiers properly to defend against SQL injection.

db<>fiddle here (returning query string as error msg instead of executing it)
Old sqlfiddle

| improve this answer | |
1

If your table structures are the same, you could use table inheritance then create a master schema with all the table structures and have the others inherit table from that one: Here is an article we wrote describing the technique:

http://www.postgresonline.com/journal/archives/59-How-to-Inherit,-Unherit-and-Merge-Inherit.html

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.