0

I am mapping the view usage on an uknown database scheme. I was provided with a list with some views:

view_a
view_b
view_c

And I want to search my database which views depend on another views for example if view_c is generated via the following sql query:

CREATE VIEW view_c AS SELECT * from view_a JOIN view_b WHERE view_a.column=344

I want somehow to list that view_c uses view_a and view_b upon excell sheet. For now is done manually via looking the views dll sql on Dbeaver and figuring out from it. But it is very laborious job and may miss some view dependencies as well, so do you have any idea how If I can generate the excell sheet via a query from information_schema.* tables?

0
1

You can use view_table_usage for that:

The following will show all objects that view_c uses.

select vtu.table_schema, 
       vtu.table_name, 
       CASE cl.relkind 
          WHEN 'r' THEN 'TABLE'
          WHEN 'i' THEN 'INDEX'
          WHEN 'S' THEN 'SEQUENCE'
          WHEN 'v' THEN 'VIEW'
          WHEN 'm' THEN 'MATERIALIZED VIEW'
          WHEN 'c' THEN 'TYPE'
          WHEN 't' THEN 'TOAST'
          WHEN 'f' THEN 'FOREIGN TABLE'
       END AS object_type, 
       obj_description(cl.oid) as remarks
from information_schema.view_table_usage vtu 
  join pg_catalog.pg_class cl on cl.oid = (quote_ident(vtu.table_schema)||'.'||quote_ident(vtu.table_name))::regclass 
where (view_schema, view_name) = ('public', 'view_c') 
order by view_schema, view_name 

If you run that query using view_a as the input, you will get the tables (and other object) that view_a uses.

1

You can get which view uses another view with the following query:

select 
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2 
where 
    pg_views.definition ilike '%'||pg_views2.viewname||'%' 
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public' and pg_views2.schemaname='public'
;

And In case you want to search a specific list of views:

select 
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2 
where 
    pg_views.definition ilike '%'||pg_views2.viewname||'%' 
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public' 
    and pg_views2.schemaname='public'
    and pg_views2.viewname in ('view_a','view_b','view_c')
;

In case that you need which materialized view uses another materialized view then you can use this query:

select 
    pg_matviews.matviewname ,
    pg_matviews2.matviewname as view_used from pg_matviews, pg_matviews as pg_matviews2 
where 
    pg_matviews.definition ilike '%'||pg_matviews2.matviewname||'%' 
    and pg_matviews.matviewname <> pg_matviews2.matviewname
    and pg_matviews.schemaname = 'public' 
    and pg_matviews2.schemaname='public'
;

The whole idea is to use either the pg_views for normal views or pg_matviews join them together and search which view used another view into their sql code.

For example in order to see which view uses another materialized view:

select 
    pg_views.viewname ,
    pg_matviews.matviewname as view_used 
from pg_views, pg_matviews 
where 
    pg_views.definition ilike '%'||pg_matviews.matviewname||'%' 
    and pg_views.schemaname = 'public' 
    and pg_matviews.schemaname='public'
;

It was tested upon postgresql 11.

2
  • 1
    You may get false positives if a view name is a substring of another view name.
    – Lennart
    Jun 23 '20 at 14:27
  • In that case I think I can use regex instead of a ilike pattern match. Jun 23 '20 at 14:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.