Hi!
I recently switched from Oracle SQL to PostgreSQL.
In Oracle, I can easily delete a table or view that is used by existing views. The system marks the affected views. I can then re-define the deleted table or view and have all dependent views easily re-compiled. Done.
PostgreSQL instead is preventing inconsistency. It simply refuses to delete a view or table that is referenced by other views. Consequently, I need to delete all dependent views first, re-define the one I want to change and then create all dependent views deleted before... - Which is much more difficult to handle.
What I especially dislike is that you cannot even insert a column into an existing view if that view is used by some other views. E.g.:
create table my_table (col1 text, col2 text);
create view my_view1 as select col1, col2 from my_table;
create view my_view2 as select col1, col2 from my_view1;
create or replace view my_view1 as select col1, col1||col2, col2 from my_table; --> ERROR: Cannot change name of view column "col2" to ..
The create or replace of view 2 fails. Clear, the manual states about create or replace view: "the new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.". Obviously, the columns are internally referenced by index, not by name. But if I want my new column between to exiting ones, I need to deleted my_view2, first...
I wonder how you deal with it in a professional way. Sounds like some type of "make" (that UNIX tool dealing with dependencies in the context of e.g. programming in C) would be helpful...
So, in an environment of rapid prototyping, if you develop the data-base design and view for tables etc. and you then need to make changes to a base table that affect all the views using it, there should be another way than doing all this manually...?!
Thx for your pointers!
I.
