From: Daniel J. Summers <daniel.lists@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Re: Querying the same column and table across schemas
Date: Fri, 05 Mar 2010 19:59:00 +0000
Message-ID: <4B916284.3040401@djs-consulting.com> (raw)
In-Reply-To: <1267818283.15754.42.camel@Family.pacifera.com>
References: <1267818283.15754.42.camel@Family.pacifera.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel.lists@djs-consulting.com
Subject: Re: Querying the same column and table across schemas
In-Reply-To: <4B916284.3040401@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox