在postgresql中查找表的所有依赖项

Question

我需要用不同结构的新表替换旧表。但是在这样做之前，我需要更改正在使用旧表的所有视图。是否有任何方法可以在postgresql中查找表的所有依赖关系。

我使用了以下脚本（来源https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/），它似乎有效，但唯一的问题是即使与表相关联的视图是22个，由于某种原因，该脚本返回44个视图（每个视图都是重复的）。

SELECT v.oid::regclass AS view
FROM pg_depend AS d      -- objects that depend on the table
   JOIN pg_rewrite AS r  -- rules depending on the table
      ON r.oid = d.objid
   JOIN pg_class AS v    -- views for the rules
      ON v.oid = r.ev_class
WHERE v.relkind = 'v'    -- only interested in views
  -- dependency must be a rule depending on a relation
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'    -- normal dependency
  AND d.refobjid = 'schema.table'::regclass;

任何想法都很有用。 提前感谢！

- Haris

---

我认为这可能会有所帮助 https://dev59.com/uHPYa4cB1Zd3GeqPiF8w - Martin Kegel

@MartinKegel 谢谢你！但不幸的是，这并没有太大帮助。 - Haris

Answer 1

pg_depend 可能包含一个表的多个条目（每个涉及的列一个）。使用 DISTINCT 以获得仅一个条目：

SELECT DISTINCT v.oid::regclass AS view
FROM pg_depend AS d      -- objects that depend on the table
   JOIN pg_rewrite AS r  -- rules depending on the table
      ON r.oid = d.objid
   JOIN pg_class AS v    -- views for the rules
      ON v.oid = r.ev_class
WHERE v.relkind = 'v'    -- only interested in views
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'    -- normal dependency
  AND d.refobjid = 'schema.table'::regclass;

- klin