Re: Querying the same column and table across schemas

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