Hi,
I am running Postgres 12.9 on FreeBSD.
I had a developer ask me the best way to determine a view's dependencies (i.e. which tables/views are used in a view's query). In Oracle, I would use the ALL_DEPENDENCIES dictionary view. I found a view called information_schema.view_table_usage (VTU) in the Postgres docs. This does the trick. However, the view the developer is interested in is not listed when I query the VTU view.
Looking at the documentation for the VTU view, it does state - "A table is only included if that table is owned by a currently enabled role."
I am able to look at the view's query by using "\d+ schema_name.view_name" and can see which objects are associated with the view. I have confirmed I am logged in as the role that owns all associated objects (i.e. the view, plus all objects that are part of the view's SELECT.).
If I run the following:
select viewname from pg_views where schemaname = 'event' order by 1;
I see there are 7 views in the event schema. However, when I run this:
select distinct view_name from information_schema.view_table_usage where view_schema = 'event' order by 1;
There are only 6 views listed. And the one that I'm interested in is, of course, not listed.
I've also tried playing around with the search_path (e.g. making sure it includes the schemas of all objects associated with the view), but still no luck. Every time I query VTU, it only shows 6 of the 7 views in the event schema.
Has anybody ever encountered this, and if so, did you find a resolution? Or perhaps there other limitations with the VTU that I'm unaware of?
Thanks!
Jon