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

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.