Re: ALTER TABLE should change respective views

On Monday 04 May 2009 23:11:22 Archana Sundararam wrote:
> I have many views dependent on a table. So whenever I do alter table and
> change the column type I have to drop and recreate all the views. Is there
> any other easy way to propagate the changes in the table to the views. Any
> suggestion is welcome.

Consider this example:

CREATE TABLE tab1 (   a int,   b text
);

CREATE VIEW view1 AS SELECT a, foo(b) FROM tab1;

ALTER TABLE tab1 ALTER COLUMN b TYPE inet;

Now what do expect should become of the view?

CREATE VIEW view1 AS SELECT a, foo(b) FROM tab1;  -- now using foo(inet)

or

CREATE VIEW view1 AS SELECT a, foo(b::text) FROM tab1; -- still using 
foo(text)

(This becomes more entertaining if you specified a conversion function (USING) 
for the type change.)

And this could then also change the return type of foo(), thus changing the 
row type of the view and would thus propogate up to other views.  And so if 
you have "many views", as you say, this could become a great mess.  You could 
probably define and implement a solution, but it would be very confusing and 
risky to use.