Find all dependencies of a table in postgresql-CodePudding

I need to replace a old table with a new one with a different structure. But before doing that i need to change all the views that are using the old table. Is there any way to find all the dependencies of a table in postgresql. I used the script below (source https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/) and it seems to work but the only problem is that even though the views that are connected with the table are 22 for some reason the script returns 44 views(each one of the view as duplicate).

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;

Any idea would be useful. Thanks in advance!

CodePudding user response：

pg_depend may contain many entries for a single table (one for each column involved). Use DISTINCT to get only one entry:

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;