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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
