Get all recursive dependencies of a single database object

Question

I've written a SELECT statement that creates a List of all objects that depend on a single object, so that if I wanted to DROP that object I could DROP all referenced objects first without using CASCADE.

It's a long script and I'm not sure if it's gonna work in every situation, so I just wanted to ask if I've missed something or if there is a way to optimize / shorten this script.

WITH RECURSIVE dep_recursive AS (
 -- Recursion: Initial Query
 SELECT
 0 AS "level",
 'enter_object_name' AS "dep_name", -- <- define dependent object HERE
 '' AS "dep_table",
 '' AS "dep_type",
 '' AS "ref_name",
 '' AS "ref_type"
 UNION ALL
 -- Recursive Query
 SELECT
 level + 1 AS "level",
 depedencies.dep_name,
 depedencies.dep_table,
 depedencies.dep_type,
 depedencies.ref_name,
 depedencies.ref_type
 FROM (
 -- This function defines the type of any pg_class object
 WITH classType AS (
 SELECT
 oid,
 CASE relkind
 WHEN 'r' THEN 'TABLE'::text
 WHEN 'i' THEN 'INDEX'::text
 WHEN 'S' THEN 'SEQUENCE'::text
 WHEN 'v' THEN 'VIEW'::text
 WHEN 'c' THEN 'TYPE'::text -- note: COMPOSITE type
 WHEN 't' THEN 'TABLE'::text -- note: TOAST table
 END AS "type"
 FROM pg_class
 )
 -- Note: In pg_depend, the triple (classid,objid,objsubid) describes some object that depends
 -- on the object described by the tuple (refclassid,refobjid).
 -- So to drop the depending object, the referenced object (refclassid,refobjid) must be dropped first
 SELECT DISTINCT
 -- dep_name: Name of dependent object
 CASE classid
 WHEN 'pg_class'::regclass THEN objid::regclass::text
 WHEN 'pg_type'::regclass THEN objid::regtype::text
 WHEN 'pg_proc'::regclass THEN objid::regprocedure::text
 WHEN 'pg_constraint'::regclass THEN (SELECT conname FROM pg_constraint WHERE OID = objid)
 WHEN 'pg_attrdef'::regclass THEN 'default'
 WHEN 'pg_rewrite'::regclass THEN (SELECT ev_class::regclass::text FROM pg_rewrite WHERE OID = objid)
 WHEN 'pg_trigger'::regclass THEN (SELECT tgname FROM pg_trigger WHERE OID = objid)
 ELSE objid::text 
 END AS "dep_name",
 -- dep_table: Name of the table that is associated with the dependent object (for default values, triggers, rewrite rules)
 CASE classid
 WHEN 'pg_constraint'::regclass THEN (SELECT conrelid::regclass::text FROM pg_constraint WHERE OID = objid)
 WHEN 'pg_attrdef'::regclass THEN (SELECT adrelid::regclass::text FROM pg_attrdef WHERE OID = objid)
 WHEN 'pg_trigger'::regclass THEN (SELECT tgrelid::regclass::text FROM pg_trigger WHERE OID = objid)
 ELSE ''
 END AS "dep_table",
 -- dep_type: Type of the dependent object (TABLE, FUNCTION, VIEW, TYPE, TRIGGER, ...)
 CASE classid
 WHEN 'pg_class'::regclass THEN (SELECT TYPE FROM classType WHERE OID = objid)
 WHEN 'pg_type'::regclass THEN 'TYPE'
 WHEN 'pg_proc'::regclass THEN 'FUNCTION'
 WHEN 'pg_constraint'::regclass THEN 'TABLE CONSTRAINT'
 WHEN 'pg_attrdef'::regclass THEN 'TABLE DEFAULT'
 WHEN 'pg_rewrite'::regclass THEN (SELECT TYPE FROM classType WHERE OID = (SELECT ev_class FROM pg_rewrite WHERE OID = objid))
 WHEN 'pg_trigger'::regclass THEN 'TRIGGER'
 ELSE objid::text
 END AS "dep_type",
 -- ref_name: Name of referenced object (the object that depends on the dependent object)
 CASE refclassid
 WHEN 'pg_class'::regclass THEN refobjid::regclass::text
 WHEN 'pg_type'::regclass THEN refobjid::regtype::text
 WHEN 'pg_proc'::regclass THEN refobjid::regprocedure::text
 ELSE refobjid::text
 END AS "ref_name",
 -- ref_type: Type of the referenced object (TABLE, FUNCTION, VIEW, TYPE, TRIGGER, ...)
 CASE refclassid
 WHEN 'pg_class'::regclass THEN (SELECT TYPE FROM classType WHERE OID = refobjid)
 WHEN 'pg_type'::regclass THEN 'TYPE'
 WHEN 'pg_proc'::regclass THEN 'FUNCTION'
 ELSE refobjid::text
 END AS "ref_type",
 -- dependency type: Only 'normal' dependencies are relevant for DROP statements
 CASE deptype
 WHEN 'n' THEN 'normal'
 WHEN 'a' THEN 'automatic'
 WHEN 'i' THEN 'internal'
 WHEN 'e' THEN 'extension'
 WHEN 'p' THEN 'pinned'
 END AS "dependency type"
 FROM pg_catalog.pg_depend
 WHERE deptype = 'n' -- look at normal dependencies only
 AND refclassid NOT IN (2615, 2612) -- schema and language are ignored as dependencies
 ) depedencies
 -- Recursion: Join with results of last query, search for dependencies recursively
 JOIN dep_recursive ON (dep_recursive.dep_name = depedencies.ref_name)
 WHERE depedencies.ref_name NOT IN(depedencies.dep_name, depedencies.dep_table) -- no self-references
)
-- Select and filter the results of the recursive query
SELECT
 MAX(level) AS "level", -- drop highest level first, so no other objects depend on it
 dep_name, -- the object to drop
 MIN(dep_table) AS "dep_table", -- the table that is associated with this object (constraints, triggers)
 MIN(dep_type) AS "dep_type", -- the type of this object
 string_agg(ref_name, ', ') AS "ref_names", -- list of objects that depend on this (just FYI)
 string_agg(ref_type, ', ') AS "ref_types" -- list of their respective types (just FYI)
FROM dep_recursive
WHERE level > 0 -- ignore the initial object (level 0)
GROUP BY dep_name -- ignore multiple references to dependent objects, dropping them once is enough
ORDER BY level desc, dep_name; -- level descending: deepest dependency first

• anon: I'm having difficulties to make this work on version 9.2.x. I have no result, this seems to be tied to dependent object name format. Could you give some example ? I'm trying to use this script to find the dependee of a table.
• MattG: With Redshift I am getting "[Amazon](500310) Invalid operation: cannot cast type regclass to character varying;"

Answer 1

Looks good! Seemed to work for me, except in postgres 9.3 (harmless in previous) add:

 WHEN 'm' THEN 'MATERIALIZED VIEW'::text

in the classType subquery.

• shaunc: Btw ... what I would really like is a script which, given a new definition for a materialized view which doesn't affect the definition of dependents, would (in a transaction), drop dependents, alter original, and recreate dependents.
• Malachi: could you elaborate a little more on your answer please?