Обсуждение: inserting a column into a view
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. I wondered if there was any clever way around this tedious work? Thanks in anticipation. Regards richard
Richard Terry <rterry@internode.on.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
Tom Lane wrote:
REgards
richard
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!).Richard Terry <rterry@internode.on.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
REgards
richard