Re: single sql, multiple schemas, one result set

[Rob Sargent]

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";