Re: Querying the same column and table across schemas - Mailing list pgsql-admin

On 03/05/2010 07:44 PM, John A. Sullivan III wrote:
> I'm trying to avoid making a thousand call like
>
> select user1.session_id from user1.sessions;
>
> when I could do it in a single query especially since the database is
> remote and secured with SSL.
>
CREATE VIEW all_sessions AS
SELECT user1.session_id, 1 as user_number
FROM user1.sessions
UNION
SELECT user2.session_id, 2 AS user_number
FROM user2.sessions
UNION
...more schemas...

Then, "SELECT * FROM all_sessions" would show you each session ID (and,
with the user_number field, what user - you could use a string literal
there too).  Of course, the user creating and running this would need
SELECT privileges on each schema's "sessions" table.


Daniel