Pg depend display

From PostgreSQL wiki

Jump to: navigation, search

Administrative Snippets

Dependency display

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing


See also: http://archives.postgresql.org/pgsql-hackers/2011-01/msg00894.php 

SELECT classid::regclass AS "depender object class",
    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
        ELSE objid::text 
    END AS "depender object identity",
    objsubid,
    refclassid::regclass AS "referenced object class",
    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 "referenced object identity",
    refobjsubid,
    CASE deptype
        WHEN 'p' THEN 'pinned'
        WHEN 'i' THEN 'internal'
        WHEN 'a' THEN 'automatic'
        WHEN 'n' THEN 'normal'
    END AS "dependency type"
FROM pg_catalog.pg_depend ;

If you are interested only in dependencies on user objects only (not system objects), add 

WHERE objid >= 16384 OR refobjid >= 16384

or some such.

Note that this doesn't readily work in 8.2 and earlier, because the various regXXX types cannot be casted to text. As a workaround, you can use this: 

SELECT classid::regclass AS "depender object class",
    CASE classid
        WHEN 'pg_class'::regclass THEN textin(regclassout(objid::regclass))
        WHEN 'pg_type'::regclass THEN textin(regtypeout(objid::regtype))
        WHEN 'pg_proc'::regclass THEN textin(regprocedureout(objid::regprocedure))
        ELSE objid::text 
    END AS "depender object identity",
    objsubid,
    refclassid::regclass AS "referenced object class",
    CASE refclassid
        WHEN 'pg_class'::regclass THEN textin(regclassout(refobjid::regclass))
        WHEN 'pg_type'::regclass THEN textin(regtypeout(refobjid::regtype))
        WHEN 'pg_proc'::regclass THEN textin(regprocedureout(refobjid::regprocedure))
        ELSE refobjid::text
    END AS "referenced object identity",
    refobjsubid,
    CASE deptype
        WHEN 'p' THEN 'pinned'
        WHEN 'i' THEN 'internal'
        WHEN 'a' THEN 'automatic'
        WHEN 'n' THEN 'normal'
    END AS "dependency type"
FROM pg_catalog.pg_depend ;
Retrieved from "http://wiki.postgresql.org/wiki/Pg_depend_display"
Personal tools