DB view dependencies in PostgreSQL

example.sql

create table departments
(
  dept_id integer primary key,
  dept_name varchar(255)
);

create table employees
(
  emp_id integer primary key,
  emp_name varchar(255),
  dept_id integer,
  constraint emp_dept_fkey foreign key(dept_id) references departments(dept_id)
);

create table sales
(
  sale_id integer primary key,
  sale_value numeric,
  dept_id integer,
  constraint sale_dept_fkey foreign key(dept_id) references departments(dept_id)
);

create view v_department_employees as
select d.dept_id, d.dept_name, count(1) num_employees
from departments d
join employees e on d.dept_id = e.dept_id
group by d.dept_id, d.dept_name;

create view v_department_sales as
select d.dept_id, d.dept_name, sum(sale_value) sum_sales
from departments d
join sales s on d.dept_id = s.dept_id
group by d.dept_id, d.dept_name;

create view v_department_sales_per_employee as
select de.dept_id, de.dept_name, de.num_employees, ds.sum_sales, ds.sum_sales / de.num_employees sales_per_emp
from v_department_employees de
join v_department_sales ds on de.dept_id = ds.dept_id;

create view v_top_department as
select dspe.dept_id, dspe.dept_name, dspe.num_employees, dspe.sum_sales, dspe.sales_per_emp
from v_department_sales_per_employee dspe
order by sales_per_emp desc
limit 1;

insert into departments(dept_id, dept_name) values (1, 'Dept 1');
insert into departments(dept_id, dept_name) values (2, 'Dept 2');

insert into employees(emp_id, emp_name, dept_id) values (10, 'Emp 10', 1);
insert into employees(emp_id, emp_name, dept_id) values (11, 'Emp 11', 1);
insert into employees(emp_id, emp_name, dept_id) values (20, 'Emp 20', 2);
insert into employees(emp_id, emp_name, dept_id) values (21, 'Emp 21', 2);
insert into employees(emp_id, emp_name, dept_id) values (22, 'Emp 22', 2);

insert into sales(sale_id, sale_value, dept_id) values (1, 1.0, 1);
insert into sales(sale_id, sale_value, dept_id) values (10, 10.0, 1);
insert into sales(sale_id, sale_value, dept_id) values (100, 100.0, 1);
insert into sales(sale_id, sale_value, dept_id) values (2, 2.0, 2);
insert into sales(sale_id, sale_value, dept_id) values (20, 20.0, 2);

--
with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, ind, ord) as 
(
  select vtu_parent.view_schema, vtu_parent.view_name, 
    vtu_parent.table_schema, vtu_parent.table_name, 
    '', array[row_number() over (order by view_schema, view_name)]
  from information_schema.view_table_usage vtu_parent
  where vtu_parent.view_schema = 'public' and vtu_parent.view_name = 'v_top_department'
  union all
  select vtu_child.view_schema, vtu_child.view_name, 
    vtu_child.table_schema, vtu_child.table_name, 
    vtu_parent.ind || '  ', 
    vtu_parent.ord || (row_number() over (order by view_schema, view_name))
  from view_tree vtu_parent, information_schema.view_table_usage vtu_child
  where vtu_child.view_schema = vtu_parent.child_schema 
  and vtu_child.view_name = vtu_parent.child_obj
) 
select tree.ind || tree.parent_schema || '.' || tree.parent_obj 
  || ' depends on ' || tree.child_schema || '.' || tree.child_obj txt, tree.ord
from view_tree tree
order by ord;