Subject: Re: Querying the same column and table across schemas
From: "John A. Sullivan III" <jsullivan@opensourcedevel.com>
To: Bob Lunney <bob_lunney@yahoo.com>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <576643.83333.qm@web39701.mail.mud.yahoo.com>
References: <576643.83333.qm@web39701.mail.mud.yahoo.com>
Content-Type: text/plain
Date: Fri, 05 Mar 2010 17:39:51 -0500
Message-Id: <1267828791.15754.53.camel@Family.pacifera.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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 central admin 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