From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: single sql, multiple schemas, one result set |
Date: | 2018-04-03 18:12:11 |
Message-ID: | 22e7e414-0a46-faf1-6669-c786544b60d6@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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";
From | Date | Subject | |
---|---|---|---|
Next Message | Artur Formella | 2018-04-03 20:01:20 | Concurrent CTE |
Previous Message | Guyren Howe | 2018-04-03 18:05:55 | Re: single sql, multiple schemas, one result set |