From: John A. Sullivan III <jsullivan@opensourcedevel.com>
To: Bob Lunney <bob_lunney@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Querying the same column and table across schemas
Date: Fri, 05 Mar 2010 17:39:51 -0500
Message-ID: <1267828791.15754.53.camel@Family.pacifera.com> (raw)
In-Reply-To: <576643.83333.qm@web39701.mail.mud.yahoo.com>
References: <576643.83333.qm@web39701.mail.mud.yahoo.com>
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
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: jsullivan@opensourcedevel.com, bob_lunney@yahoo.com
Subject: Re: Querying the same column and table across schemas
In-Reply-To: <1267828791.15754.53.camel@Family.pacifera.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