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

Re: inserting a column into a view

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Terry <rterry(at)internode(dot)on(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: inserting a column into a view
Date: 2012-06-12 22:44:48
Message-ID: 25418.1339541088@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Jeff DavisDate: 2012-06-12 23:30:39
Subject: Re: coalesce in plpgsql, and other style questions
Previous:From: Richard TerryDate: 2012-06-12 22:12:14
Subject: inserting a column into a view

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