Message-ID: <4B919DC8.5080307@djs-consulting.com>
Date: Sat, 06 Mar 2010 00:11:52 +0000
From: "Daniel J. Summers" <daniel.lists@djs-consulting.com>
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>	
	<4B916284.3040401@djs-consulting.com>
	<1267821889.15754.49.camel@Family.pacifera.com>
In-Reply-To: <1267821889.15754.49.camel@Family.pacifera.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 03/05/2010 08:44 PM, John A. Sullivan III wrote:
> On Fri, 2010-03-05 at 19:59 +0000, Daniel J. Summers wrote:
>    
>> 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
>> ...
>>      
> That sounds quite reasonable.  I'm guessing that a view is superior to
> creating a new schema with tables derived from selects from all the
> schemas because it would be less overhead and dynamic, i.e., I only
> create the view once and it always has the most current data.  Is that
> correct?
>    

Right - the view is the window to the tables.  It will query each of 
those, so it might take some time - but, hopefully the session tables 
would be pretty small, so it should run adequately.

> As we add new schemas, is there an easy way to update the view? That was
> not obvious to me looking at the documentation for ALTER VIEW and CREATE
> OR REPLACE VIEW seems to be sensitive to ensuring the new view is
> identical to the old except for appends.  It would be nice if we could
> simply append
> UNION SELECT * from user3.sessions
> to the view.

What I usually do with views is simply save the SQL in source control, 
then append a drop right in front of it (i.e., "DROP VIEW view_name; 
CREATE...").  I actually end up saving it like that because, as I'm 
developing it, I often don't get it right the first time.  :)  As there 
are no foreign key constraints to worry about with views, they can be 
dropped and recreated pretty easily.


Daniel