I think something like this should do it:
SELECT DISTINCT
pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
pg_depend.classid = 'pg_rewrite'::regclass AND
pg_depend.objid = pg_rewrite.oid AND
pg_depend.refclassid = 'pg_class'::regclass AND
pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
pg_class.oid = pg_depend.refobjid AND
pg_class.relkind IN ('r','f','p','v','m')
WHERE
pg_rewrite.ev_class = 'rental_by_category'::regclass
This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE
CTE).
The pg_class.relkind IN ('r','f','p','v','m')
constraint listscovers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docsdocs.
Many different kinds of dependencies are stored in pg_depend
, and not all of them are relevant to your question. For example, the view's pg_rewrite
entry has a dependency on its own pg_class
entry; the pg_depend.refobjid <> pg_rewrite.ev_class
constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.