Re: single sql, multiple schemas, one result set

Rob Sargent <robjsargent@xxxxxxxxx> · Tue, 3 Apr 2018 12:12:11 -0600

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