如何编写PostgreSQL查询以列出指定物化视图依赖的表？并解释原理

针对示例物化视图的查询语句 #

要列出films_ext物化视图依赖的所有表，可以使用以下查询：

SELECT 
    c.relname AS dependent_table
FROM 
    pg_depend d
JOIN 
    pg_class c ON d.refobjid = c.oid
JOIN 
    pg_class mv ON d.objid = mv.oid
WHERE 
    mv.relname = 'films_ext'
    AND mv.relkind = 'm'
    AND c.relkind IN ('r', 'p')
ORDER BY 
    dependent_table;

运行后会返回：

dependent_table
----------------
distributors
films

工作原理详解 #

PostgreSQL通过内置系统表跟踪所有数据库对象的依赖关系，这个查询核心依赖两个关键系统表：

1. 核心系统表说明 #

• pg_class：存储所有关系型对象（表、物化视图、视图等）的元数据。其中relkind字段用于区分对象类型：
  • 'm' = 物化视图
  • 'r' = 普通表
  • 'p' = 分区表
• pg_depend：记录对象间的依赖关联，objid是依赖方（这里是物化视图）的对象ID，refobjid是被依赖方（这里是表）的对象ID。

2. 查询逻辑步骤 #

1. 在pg_class中定位目标物化视图films_ext的对象ID（oid），通过relname = 'films_ext'和relkind = 'm'过滤。
2. 关联pg_depend表，找到所有该物化视图依赖的对象ID（refobjid）。
3. 再次关联pg_class，将refobjid转换为对应的表名称（relname），并通过relkind IN ('r', 'p')只保留普通表和分区表。
4. 最后按表名称排序输出结果。

更直观的替代查询 #

也可以使用专门存储物化视图信息的pg_matviews视图来简化查询：

SELECT 
    c.relname AS dependent_table
FROM 
    pg_matviews mv
JOIN 
    pg_depend d ON mv.oid = d.objid
JOIN 
    pg_class c ON d.refobjid = c.oid
WHERE 
    mv.matviewname = 'films_ext'
    AND c.relkind IN ('r', 'p')
ORDER BY 
    dependent_table;

这个方式直接通过pg_matviews的matviewname字段定位目标物化视图，逻辑更清晰，结果和前一个查询完全一致。

内容的提问来源于stack exchange，提问作者KrzyBoch