我需要把一张旧桌子换成一张 struct 不同的新桌子.但在此之前,我需要更改使用旧表的所有视图.有没有办法在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;
任何 idea 都将是有用的. 提前谢谢!