DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=0oXfXHc1F33z3b41QG/kvtbYv1W3dCRq1YFezwAz+d6nQ24OhkSJLbDQIyE+oFcEXQQOa+8nXfBw13eB97OVDHiqPMR1lgK55MNLsKciXwuNHMkFiTpVT01Hm6wM5oxa6SZXDSM2Bk0ZjIkXKe7RmnNzAmT/PXpDRtolQPPzZTw=;
Message-ID: <576643.83333.qm@web39701.mail.mud.yahoo.com>
Date: Fri, 5 Mar 2010 14:13:51 -0800 (PST)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Querying the same column and table across schemas
To: pgsql-admin@postgresql.org
In-Reply-To: <1267818283.15754.42.camel@Family.pacifera.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0A=0A--- On Fri, 3/5/10, John A. Sullivan III <jsullivan@opensourcedevel.c=
om> wrote:=0A=0A> From: John A. Sullivan III <jsullivan@opensourcedevel.com=
>=0A> Subject: [ADMIN] Querying the same column and table across schemas=0A=
> To: pgsql-admin@postgresql.org=0A> Date: Friday, March 5, 2010, 2:44 PM=
=0A> Hello, all.=A0 I'm working on a=0A> project using the X2Go terminal se=
rver=0A> project (www.x2go.org).=A0 They record session data in a=0A> postg=
resql=0A> database.=A0 Our environment is a little more secure=0A> than typ=
ical and we=0A> do not want it possible for one user to see another's=0A> s=
ession data.=A0 We=0A> thus have divided the session database into schemas =
each=0A> with an=0A> identical set of tables.=A0 Each user only writes and=
=0A> reads from their=0A> schema.=0A> =0A> However, we need to query all sc=
hemas as if they were=0A> one.=A0 Is there a=0A> way to do that?=0A> =0A> I=
n other words, if we were a single schema database, we=0A> could do=0A> =0A=
> select session_id from sessions;=0A> =0A> to list all sessions.=A0 How ca=
n we accomplish the same=0A> thing to list all=0A> the sessions across all =
the schemas in a single query?=0A> =0A> I'm trying to avoid making a thousa=
nd call like=0A> =0A> select user1.session_id from user1.sessions;=0A> =0A>=
 when I could do it in a single query especially since the=0A> database is=
=0A> remote and secured with SSL.=0A> =0A> Thanks - John=0A> =0A=0AJohn,=0A=
=0AHow about creating a central admin schema and putting a trigger on all t=
he sessions tables to write changes to the central admin schema's session t=
able?  The function could belong to the admin role and run with definer's s=
ecurity.=0A=0ABob=0A=0A=0A