VIEW AS SELECT -vs- INSERT INTO table query

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsqlnovice <pgsql-novice(at)postgresql(dot)org>
Subject: VIEW AS SELECT -vs- INSERT INTO table query
Date: 2005-11-11 15:13:54
Message-ID: 5.2.1.1.0.20051111094611.02dfcec0@pop6.sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a table of items with about 50 columns, one of which is 'owner'. I
would like to run several queries where the items for several 'owners' are
to be treated as one owner. I have considered several options, but they
all have problems:

1) Create a view.
CREATE VIEW itemcombo AS SELECT 'Combined', [columns] FROM items WHERE
owner IN (...);
where [columns] is everything *except* owner? Is there a syntax for this
command that would avoid typing all 50 columns? If I add a new column to
underlying table, then view needs to be replaced? Is it possible to create
this view in such a way that it could handle "normal" queries of the
underlying table as well (where owner <> 'Combined')?

2) Create a new owner 'Combined' and use "INSERT INTO table query" to
populate items under this owner.
INSERT INTO items SELECT 'Combined', [columns] FROM items WHERE owner IN
(...);
Again, we have a problem with all those columns. Also, this command needs
to be rerun whenever underlying table is modified (which, luckily, is not
often).

3) Push the problem to user code. Every query contains "WHERE owner IN
(...)" instead of "WHERE owner='Combined'".

Are there alternate solutions that I've missed?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Emiliano Amilcarelli 2005-11-11 15:43:29 Loop through records
Previous Message Info 2005-11-10 19:30:30 Problem Upgrading from 8.0 to 8.1 (WinXP)