Thread: inserting a column into a view

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.

I wondered if there was any clever way around this tedious work?

Thanks in anticipation.

Regards

richard

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

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