Query across multiple schemas, unknown until execution time

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.

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:

• 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 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

StackExchange Database Administrators Q#61525, answer score: 11