1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
|
WITH f AS (
SELECT
pgd.objid,
pgd.classid::regclass Source_Class,
case pgd.classid
when 'pg_class'::regclass then pgd.objid::regclass::text
when 'pg_type'::regclass then pgd.objid::regtype::text
when 'pg_proc'::regclass then pgd.objid::regprocedure::text
when 'pg_namespace'::regclass then pgd.objid::regnamespace::text
when 'pg_operator'::regclass then pgd.objid::regoperator::text
when 'pg_authid'::regclass then pgd.objid::regrole::text
when 'pg_ts_config'::regclass then pgd.objid::regconfig::text
when 'pg_ts_dict'::regclass then pgd.objid::regdictionary::text
else pgd.objid::text
end Source_Object,
pgd.objsubid,
pgd.refclassid::regclass Referenced_Class,
case pgd.refclassid
when 'pg_class'::regclass then pgd.refobjid::regclass::text
when 'pg_type'::regclass then pgd.refobjid::regtype::text
when 'pg_proc'::regclass then pgd.refobjid::regprocedure::text
when 'pg_namespace'::regclass then pgd.refobjid::regnamespace::text
when 'pg_operator'::regclass then pgd.refobjid::regoperator::text
when 'pg_authid'::regclass then pgd.refobjid::regrole::text
when 'pg_ts_config'::regclass then pgd.refobjid::regconfig::text
when 'pg_ts_dict'::regclass then pgd.refobjid::regdictionary::text
else pgd.refobjid::text
end Referenced_Object,
pgd.refobjsubid,
case pgd.deptype
when 'p' then 'pinned'
when 'i' then 'internal'
when 'a' then 'automatic'
when 'n' then 'normal'
end DepType
FROM pg_catalog.pg_depend pgd
WHERE
( refobjid::regclass::text ~ '${schema}'
OR refobjid::regtype::text ~ '${schema}'
OR refobjid::regprocedure::text ~ '${schema}'
OR refobjid::regnamespace::text ~ '${schema}'
OR refobjid::regoperator::text ~ '${schema}'
OR refobjid::regrole::text ~ '${schema}'
OR refobjid::regconfig::text ~ '${schema}'
OR refobjid::regdictionary::text ~ '${schema}'
OR refobjid::text ~ '${schema}'
)
AND objid::regclass::text !~ '${schema}' AND objid::regclass::text !~ 'pg_toast'
AND objid::regtype::text !~ '${schema}' AND objid::regtype::text !~ 'pg_toast'
AND objid::regprocedure::text !~ '${schema}' AND objid::regprocedure::text !~ 'pg_toast'
AND objid::regnamespace::text !~ '${schema}' AND objid::regnamespace::text !~ 'pg_toast'
AND objid::regoperator::text !~ '${schema}' AND objid::regoperator::text !~ 'pg_toast'
AND objid::regrole::text !~ '${schema}' AND objid::regrole::text !~ 'pg_toast'
AND objid::regconfig::text !~ '${schema}' AND objid::regconfig::text !~ 'pg_toast'
AND objid::regdictionary::text !~ '${schema}' AND objid::regdictionary::text !~ 'pg_toast'
AND objid::text !~ '${schema}' AND objid::text !~ 'pg_toast'
)
SELECT
f.Source_Class,
COALESCE(
ns.nspname || '.' || pc.relname,
f.Source_Object
) Source_Object,
f.objsubid,
f.Referenced_Class,
f.Referenced_Object,
f.refobjsubid,
f.DepType
FROM f
-- ::: This Works (to replace pg_rewrite oid) :::
-- LEFT OUTER JOIN pg_rewrite rw ON rw.oid = f.objid
-- LEFT OUTER JOIN pg_class pc ON pc.oid = rw.ev_class
-- LEFT OUTER JOIN pg_namespace ns ON ns.oid = pc.relnamespace
-- ::: This doesn't (to replace pg_constraint oid) :::
LEFT OUTER JOIN pg_constraint cs ON cs.oid = f.objid
LEFT OUTER JOIN pg_class pc ON pc.oid = cs.conrelid
LEFT OUTER JOIN pg_namespace ns ON ns.oid = cs.connamespace
ORDER BY refobjsubid DESC |
Partager