Change view definition - do not have to drop it

Emi Lu <emilu@encs.concordia.ca> writes:

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.

regards, tom lane

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

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@encs.concordia.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@postgresql.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.