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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2012-06-12 23:30:39 | Re: coalesce in plpgsql, and other style questions |
Previous Message | Richard Terry | 2012-06-12 22:12:14 | inserting a column into a view |