If you just need results from multiple schemas, you can re-use the same query string and set the search_pathsearch_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:
- How does the search_path influence identifier resolution and the “current schema”
- PostgreSQL and default Schemas
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 EXECUTEplpgsql 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), ' UNION-- ALLyour '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.
SQL Fiddledb<>fiddle here (returning query string as error msg instead of executing it).
Old sqlfiddle
