From: Daniel J. Summers <daniel.lists@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Re: Querying the same column and table across schemas
Date: Sat, 06 Mar 2010 00:11:52 +0000
Message-ID: <4B919DC8.5080307@djs-consulting.com> (raw)
In-Reply-To: <1267821889.15754.49.camel@Family.pacifera.com>
References: <1267818283.15754.42.camel@Family.pacifera.com>
<4B916284.3040401@djs-consulting.com>
<1267821889.15754.49.camel@Family.pacifera.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel.lists@djs-consulting.com
Subject: Re: Querying the same column and table across schemas
In-Reply-To: <4B919DC8.5080307@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox