Tom Lane wrote:
Richard Terry <rterry@internode.on.net> writes:
When you modify a table some time down the track and insert a new
column, then need to update a view, postgres always seems to insist
that you cascade drop all dependant views and rebuild them. I've got a
huge database and find I'm in this situation some years down the track
from its inceptions.
Well, any release since 8.4 should let you dodge the "cascade drop"
bit, because you can add new columns at the end of a view:
regression=# create table t(f1 int);
CREATE TABLE
regression=# create view v as select * from t;
CREATE VIEW
regression=# alter table t add column f2 text;
ALTER TABLE
regression=# create or replace view v as select * from t;
CREATE VIEW
regression=# \d v View "public.v"Column | Type | Modifiers
--------+---------+-----------f1 | integer | f2 | text |
View definition:SELECT t.f1, t.f2 FROM t;
People occasionally complain that they shouldn't have to do the CREATE
OR REPLACE step because "select * should mean returning the current set
of columns from the underlying table". However, the SQL standard says
otherwise: * is to be expanded at view definition time.
regards, tom lane
Can't thank you enough, this has been the bane of my life over time .. guess that's why I'm on the novice list (no formal programming training!).
REgards
richard