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
