Thread: single sql, multiple schemas, one result set

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

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
passedin as an argument.
 

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