Sometimes you need to alter data type or length of some table column which is used in views.This of course means to drop and recreate all those views and this also in right order. You can find some workaround in internet. But if you want to be really on the safe side you can use following select in your function.
This select gives you:
- all dependencies in views for given table column
- drop command for all those views
- create command for all those views
- hierarchy order for all those views
Use it this way:
- store result of this select in temporary table
- in cycle drop views using “execute drop_view_text” in order “hierarchy DESC”
- alter column
- in cycle create views using “execute view_definition” in order “_level ASC” – there can be views which depend on more views from lower levels
And that’s it.
with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, hierarchy, _level) as ( select vtu_parent.view_schema, vtu_parent.view_name, vtu_parent.table_schema, vtu_parent.table_name, vtu_parent.view_schema||'.'||vtu_parent.view_name||'; ' as hierarchy, 1 as _level from information_schema.view_column_usage vtu_parent where vtu_parent.table_schema = '...your_schema...' --put schema name here and vtu_parent.table_name = '...your_table...' --put table name here and vtu_parent.column_name = '...your_column...' --put column name here union all select vtu_child.view_schema, vtu_child.view_name, vtu_child.table_schema, vtu_child.table_name, vtu_parent.hierarchy||vtu_child.view_schema||'.'||vtu_child.view_name||'; ' as hierarchy, _level+1 as _level from view_tree vtu_parent, information_schema.view_column_usage vtu_child where (vtu_child.table_schema = vtu_parent.parent_schema and vtu_child.table_name = vtu_parent.parent_obj) ) select distinct tree.parent_schema as view_schema, tree.parent_obj as view_name, 'create or replace view '||tree.parent_schema||'.'||tree.parent_obj||' as '||v.view_definition as view_definition, 'drop view if exists '||tree.parent_schema||'.'||tree.parent_obj||';' as drop_view_text, tree.hierarchy, _level from view_tree tree join information_schema.views v on tree.parent_obj=v.table_name and tree.parent_schema=v.table_schema
Structure of temp table is:
create temp table tmp_depend_views ( view_schema varchar, view_name varchar, view_definition text, drop_view_text text, hierarchy text, _level integer )