Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EEA96632878 for ; Fri, 5 Mar 2010 16:08:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 88956-04 for ; Fri, 5 Mar 2010 20:07:53 +0000 (UTC) X-Greylist: delayed 00:08:44.159547 by SQLgrey-1.7.6 Received: from tcms.us (tcms.us [174.143.154.125]) by mail.postgresql.org (Postfix) with ESMTP id 63DAE63390D for ; Fri, 5 Mar 2010 16:07:56 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by tcms.us (Postfix) with ESMTP id 0794C90898 for ; Fri, 5 Mar 2010 19:59:11 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at tcms.us Received: from tcms.us ([127.0.0.1]) by localhost (tcms.us [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dirATLrv6KBb for ; Fri, 5 Mar 2010 19:59:07 +0000 (UTC) Received: from [192.168.0.102] (c-68-35-134-229.hsd1.nm.comcast.net [68.35.134.229]) (Authenticated sender: daniel@summershome.org) by tcms.us (Postfix) with ESMTPA id 581CD90847 for ; Fri, 5 Mar 2010 19:59:07 +0000 (UTC) Message-ID: <4B916284.3040401@djs-consulting.com> Date: Fri, 05 Mar 2010 19:59:00 +0000 From: "Daniel J. Summers" User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.9pre) Gecko/20100304 Shredder/3.0.4pre MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Querying the same column and table across schemas References: <1267818283.15754.42.camel@Family.pacifera.com> In-Reply-To: <1267818283.15754.42.camel@Family.pacifera.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.855 tagged_above=-10 required=5 tests=AWL=0.745, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/45 X-Sequence-Number: 33844 On 03/05/2010 07:44 PM, John A. Sullivan III wrote: > 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. > CREATE VIEW all_sessions AS SELECT user1.session_id, 1 as user_number FROM user1.sessions UNION SELECT user2.session_id, 2 AS user_number FROM user2.sessions UNION ...more schemas... Then, "SELECT * FROM all_sessions" would show you each session ID (and, with the user_number field, what user - you could use a string literal there too). Of course, the user creating and running this would need SELECT privileges on each schema's "sessions" table. Daniel