How to alter table column which is used in views

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:

1. store result of this select in temporary table
2. in cycle drop views using “execute drop_view_text” in order “hierarchy DESC”
3. alter column
4. 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
)