Re: Querying the same column and table across schemas

--- On Fri, 3/5/10, John A. Sullivan III <jsullivan(at)opensourcedevel(dot)com> wrote:

> From: John A. Sullivan III <jsullivan(at)opensourcedevel(dot)com>
> Subject: [ADMIN] Querying the same column and table across schemas
> To: pgsql-admin(at)postgresql(dot)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