postgres_views_dependency.sql

-- flat list of postgres views depencency tree, sorted by depth
SELECT * FROM (
  -- Get every view & materialized view, assign a level 0
  WITH RECURSIVE t AS (
    SELECT
      c.oid,
      c.relname,
      0 AS LEVEL
    FROM pg_class c
    JOIN pg_namespace ON c.relnamespace = pg_namespace.oid
    WHERE c.relkind IN ('v', 'm') AND pg_namespace.nspname = 'public'
    -- Union back on ourselves, increasing the level to indicate that the view is dependent
    UNION ALL
      SELECT
        c.oid,
        c.relname,
        a.level+1
      FROM t a
      JOIN pg_depend d ON d.refobjid=a.oid
      JOIN pg_rewrite w ON w.oid= d.objid AND w.ev_class!=a.oid
      JOIN pg_class c ON c.oid=w.ev_class
  )

  -- Take the max level for each view.
  SELECT relname, MAX(level) AS level
  FROM t
  GROUP BY relname
) AS q
ORDER BY q.level DESC
~