Re: inserting a column into a view

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