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
        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"


    -- Recursive Query
        level + 1 AS "level",
    FROM (

        -- This function defines the type of any pg_class object
        WITH classType AS (
                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
            -- 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
    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
  • \$\begingroup\$ 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. \$\endgroup\$ – Olivier MATROT Jul 21 '16 at 8:16

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.

| improve this answer | |
  • \$\begingroup\$ 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. \$\endgroup\$ – shaunc Oct 26 '13 at 5:30
  • \$\begingroup\$ could you elaborate a little more on your answer please? \$\endgroup\$ – Malachi Nov 18 '13 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.