I have multiple schemas that I need to query across. The schemas are not know 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 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 ...;
$$ LANGUAGE plpgpsql STRICT; be $$ LANGUAGE plpgsql STRICT;?