Skip site navigation (1) Skip section navigation (2)

Re: inserting a column into a view

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 (view raw or flat)
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


In response to

pgsql-novice by date

Next:From: Ross BoylanDate: 2012-06-13 00:42:31
Subject: Re: coalesce in plpgsql, and other style questions
Previous:From: Jeff DavisDate: 2012-06-12 23:30:39
Subject: Re: coalesce in plpgsql, and other style questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group