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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
