From: Bob Lunney <bob_lunney@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: Re: Querying the same column and table across schemas
Date: Fri, 5 Mar 2010 14:13:51 -0800 (PST)
Message-ID: <576643.83333.qm@web39701.mail.mud.yahoo.com> (raw)
In-Reply-To: <1267818283.15754.42.camel@Family.pacifera.com>
--- On Fri, 3/5/10, John A. Sullivan III <jsullivan@opensourcedevel.com> wrote:
> From: John A. Sullivan III <jsullivan@opensourcedevel.com>
> Subject: [ADMIN] Querying the same column and table across schemas
> To: pgsql-admin@postgresql.org
> Date: Friday, March 5, 2010, 2:44 PM
> Hello, all. I'm working on a
> project using the X2Go terminal server
> project (www.x2go.org). They record session data in a
> postgresql
> database. Our environment is a little more secure
> than typical and we
> do not want it possible for one user to see another's
> session data. We
> thus have divided the session database into schemas each
> with an
> identical set of tables. Each user only writes and
> reads from their
> schema.
>
> However, we need to query all schemas as if they were
> one. Is there a
> way to do that?
>
> In other words, if we were a single schema database, we
> could do
>
> select session_id from sessions;
>
> to list all sessions. How can we accomplish the same
> thing to list all
> the sessions across all the schemas in a single query?
>
> 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.
>
> Thanks - John
>
John,
How about creating a central admin schema and putting a trigger on all the sessions tables to write changes to the central admin schema's session table? The function could belong to the admin role and run with definer's security.
Bob
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: bob_lunney@yahoo.com
Subject: Re: Querying the same column and table across schemas
In-Reply-To: <576643.83333.qm@web39701.mail.mud.yahoo.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