Re: inserting a column into a view

Tom Lane wrote:
> 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
>
>
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