Re: Change view definition - do not have to drop it

I believe Postgres only checks the output types & column names for each column in the view.

If, as you suggest, you convert these in your view to a standard appropriate datatype, you could then recreate the view with different input column datatypes:

eg: in the countries_simpl table, cat is a bigint datatype, gid is an int:

bgmaps=# create view v_test as select cat from countries_simpl;
CREATE VIEW
bgmaps=# create or replace view v_test as select cat::bigint from countries_simpl;
CREATE VIEW
bgmaps=# create or replace view v_test as select (cat::char)::bigint from countries_simpl;
CREATE VIEW
bgmaps=# create or replace view v_test as select (cat::int)::bigint from countries_simpl;
CREATE VIEW
bgmaps=# create or replace view v_test as select cat::char from countries_simpl;
ERROR: cannot change data type of view column "cat"
bgmaps=# create or replace view v_test as select gid from countries_simpl;
ERROR: cannot change name of view column "cat"
bgmaps=# create or replace view v_test as select gid::bigint from countries_simpl;
ERROR: cannot change name of view column "cat"
bgmaps=# create or replace view v_test as select gid::bigint as cat from countries_simpl;
CREATE VIEW

HTH,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Emi Lu <emilu(at)encs(dot)concordia(dot)ca> 06/03/09 10:45 AM >>>

>> Now I need update view1 definition to
>> create or replace view view1 as select col1, col2 from new_table;
>
>> However, col1 in new_table is not bpchar. This gives me headache! There
>> are tens of dependent views based on view1, so I cannot just drop view1
>> and recreate it.
>
>> How I can redefine view1 without dropping it and recreate it?
>
> Cast the new column to bpchar?
>
> If you want to change the output column type of the view, you have to
> drop and recreate it.

Thank tom. Ok, I will not change view type, just keep bpchar for now.

Just a wish, if >=8.4 could allow change view type, that would be great!

--
Lu Ying

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.