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&lt;>fiddle here (returning query string as error msg instead of executing it)
Old sqlfiddle