Re: Querying the same column and table across schemas

On Fri, 2010-03-05 at 14:13 -0800, Bob Lunney wrote:
>
> --- 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
centraladmin schema's session table?  The function could belong to the admin role and run with definer's security. 
>
> Bob
>
>
>
>
That sounds ideal and perhaps lower overhead than rerunning the view
build query every time I query the view but, being a database ignoramus,
it is quite beyond my skills :-(  If I can scrape up the time, I'll do
some research on triggers and functions.  I have used functions before
but never triggers.  Thanks - John