dealing with dependencies

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.

======================================================================

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio