Postgresql is very restrictive when it comes to modifying existing
objects. Very often when you try to ALTER TABLE or REPLACE VIEW it
tells you that you cannot do it,because there’s another object
(typically a view or materialized view),which depends on the one you
want to modify. It seems that the only solution is to DROP dependent
objects,make desired changes to the target object and then recreate
dropped objects.

It is tedious and cumbersome,because those dependent objects can have
further dependencies,which also may have other dependencies and so
on. I created 07001 which can help in such situations.

The usage is very simple – you just have to call:

select deps_save_and_drop_dependencies(p_schema_name,p_object_name);

You
have to pass two arguments: the name of the schema and the name of the
object in that schema. This object can be a table,a view or a
materialized view. The function will drop all views and materialized
views dependent on p_schema_name.p_object_name and save DDL which
restores them in a helper table.

When you want to restore those dropped objects (for example when you
are done modyfing p_schema_name.p_object_name),you just need to make
another simple call:

select deps_restore_dependencies(p_schema_name,p_object_name);

and the dropped objects will be recreated.

These functions take care about:

• dependencies hierarchy
• proper order of dropping and creating views/materialized views across hierarchy
• restoring comments and grants on views/materialized views

Click 07002 for a working sqlfiddle example or check 07003 for a complete source code