Christopher Kings-Lynne wrote:
>>>We do, but as soon as you break the view by dropping an underlying
>>>object it fails to reconstruct. So having the original view definition
>>>at hand could be useful for some ALTER VIEW RECOMPILE command.
>>
>>Note that the assumptions underlying this discussion have changed in
>>CVS tip: you can't break a view by dropping underlying objects.
>>
>>regression=# create table foo(f1 int, f2 text);
>>CREATE TABLE
>>regression=# create view bar as select * from foo;
>>CREATE VIEW
>>regression=# drop table foo;
>>NOTICE: rule _RETURN on view bar depends on table foo
>>NOTICE: view bar depends on rule _RETURN on view bar
>>ERROR: Cannot drop table foo because other objects depend on it
>> Use DROP ... CASCADE to drop the dependent objects too
>
>
> Hrm - looks like we really need CREATE OR REPLACE VIEW...
The problem is that you would still need to keep a copy of your view
around to recreate it if you wanted to drop and recreate a table it
depends on. I really like the idea about keeping the original view
source handy in the system catalogs.
It is common in Oracle to have dependent objects like views and packages
get invalidated when something they depend on is dropped/recreated.
Would it make sense to do something like that? I.e. set a relisvalid
flag to false, and generate an ERROR telling you to recompile the object
if you try to use it while invalid.
Joe