--- On Fri, 3/5/10, John A. Sullivan III <jsullivan@xxxxxxxxxxxxxxxxxxx> wrote: > From: John A. Sullivan III <jsullivan@xxxxxxxxxxxxxxxxxxx> > Subject: Querying the same column and table across schemas > To: pgsql-admin@xxxxxxxxxxxxxx > 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
