Querying the same column and table across schemas

From: "John A(dot) Sullivan III" <jsullivan(at)opensourcedevel(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Querying the same column and table across schemas
Date: 2010-03-05 19:44:43
Message-ID: 1267818283.15754.42.camel@Family.pacifera.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Daniel J. Summers 2010-03-05 19:59:00 Re: Querying the same column and table across schemas
Previous Message Plugge, Joe R. 2010-03-05 13:58:28 Re: [SQL] PostgreSQL Active-Active Configuration