I have multiple schemas that I need to query across. The schemas are not known in advance, for example:
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.
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
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
EXECUTE.