REPLACE VIEWit 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
DROPdependent 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 utility functions 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_nameand 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