Re: inserting a column into a view

Richard Terry <rterry(at)internode(dot)on(dot)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