Postgresql Query Across Multiple Schemas

If you need to build queries dynamically, you have two options: prepared statements and a function.

A prepared statement only works for the duration of the session, it is lost when you log out. You create a prepared statement with the PREPARE statement and then you EXECUTE it supplying parameters such as your schema name, once for each of the schemas you want to run it on.

A function remains available in the database for future use. If you need to combine data from the different schemas into one result set, this is your only option and you should define your function to return SETOF tablename. It works more or less like this:

CREATE FUNCTION query_tablename_schemas (schemas name[]) RETURNS SETOF tablename AS $$
DECLARE
  sch name;
BEGIN
  FOREACH sch IN ARRAY schemas LOOP
    RETURN QUERY 'SELECT * FROM ' || quote_ident(sch) || '.tablename WHERE ...';
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

The above assumes that you can pass your schemas in an array. If you have some query with which you can retrieve the schema names, then you can change the loop and do something like:

FOR sch IN
  SELECT schema_name FROM ...
LOOP
  qry := ...
  RETURN QUERY qry;
END LOOP;

In both cases, records from all tables with name tablename in all schemas are returned as a table that you can then use in other queries:

SELECT *
FROM query_tablename_schemas ('{"U111", "U222", "U333"}')
WHERE ...;