single sql, multiple schemas, one result set

Make a view that joins all the things, with a column providing the name of the schema that they came from.

On 04/03/2018 11:47 AM, PegoraroF10 wrote:

Suppose a DB with dozens of schemas with same structure.
DB
Schema1
Table1
Table2
Schema2
Table1
Table2
Schema3
Table1
Table2
Then we want to execute a SQL on specific schemas and the result of it could
be a UNION ALL. So, how could be a function that runs that SQL on each
schema and results just on result set ?

Then I would use something like ...
select * from SQLFromMultipleShema(Array['Schema1,Schema3'],'select count(*)
from Table2');

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Should work if you add a tag to every table referenced in the sql sent in:

select * from SQLFromMultipleShema(Array['Schema1,Schema3'],'select count(*)
from SCHEMA_NAME.Table2')

Then, in a loop over the schema name array replacing the SCHEMA_NAME as you build up a union of your injected sql.

Or perhaps loop over the schame name array reseting search_path as you build up a temp table of the result from the sql passed in as an argument.

Pretty sure you're going to end up need "dynamic sql";