On Fri, 2010-03-05 at 14:13 -0800, Bob Lunney wrote: > > --- 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 > > > > 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
