From: | Richard Terry <richard(at)pacific(dot)net(dot)au> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: inserting a column into a view |
Date: | 2012-06-13 00:33:21 |
Message-ID: | 4FD7DFD1.9090401@pacific.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ross Boylan | 2012-06-13 00:42:31 | Re: coalesce in plpgsql, and other style questions |
Previous Message | Jeff Davis | 2012-06-12 23:30:39 | Re: coalesce in plpgsql, and other style questions |