Get base table from materialized view in postgresql

Question

I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i retrieve the list of base tables from "definition"?

example query materialized view :

CREATE MATERIALIZED VIEW rental_by_category
AS
SELECT c.name AS category,
 sum(p.amount) AS total_sales
 FROM (((((payment p
 JOIN rental r ON ((p.rental_id = r.rental_id)))
 JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
 JOIN film f ON ((i.film_id = f.film_id)))
 JOIN film_category fc ON ((f.film_id = fc.film_id)))
 JOIN category c ON ((fc.category_id = c.category_id)))
 GROUP BY c.name
 ORDER BY sum(p.amount) DESC
WITH NO DATA;

i want to get list of materialized view like this:

rental_by_category

base table
payment
rental
inventory
film
film_category
category

• Erwin Brandstetter: Related: dba.stackexchange.com/q/158812/3684

Answer 1

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).

pg_class.relkind IN ('r','f','p','v','m') covers 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 docs.

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.