Postgres – get public dependencies

readme.md

Select dependencies between objects in the public namespace.

query.pgsql

SELECT DISTINCT
  d.deptype,

  from_table.relname AS classid,
  COALESCE(from_class.relname, from_type.typname, from_proc.proname) AS objid,
  from_attr.attname AS objsubid,

  to_table.relname AS refclassid,
  COALESCE(to_class.relname, to_type.typname, to_proc.proname, to_ext.extname, to_con.conname) AS refobjid,
  to_attr.attname AS refobjsubid

FROM pg_depend d

-- The dependent object.
LEFT JOIN pg_attrdef from_ad
  ON d.classid = 'pg_attrdef'::regclass
  AND from_ad.oid = d.objid
LEFT JOIN pg_rewrite from_rw
  ON d.classid = 'pg_rewrite'::regclass
  AND from_rw.oid = d.objid
LEFT JOIN pg_class from_class
  ON from_class.oid = COALESCE(from_rw.ev_class, from_ad.adrelid, d.objid)
LEFT JOIN pg_type from_type
  ON d.classid = 'pg_type'::regclass
  AND from_type.oid = d.objid
LEFT JOIN pg_proc from_proc
  ON d.classid = 'pg_proc'::regclass
  AND from_proc.oid = d.objid
LEFT JOIN pg_attribute from_attr
  ON (d.objsubid <> 0 OR from_ad.adrelid IS NOT NULL)
  AND from_attr.attnum = COALESCE(from_ad.adnum, d.objsubid)
  AND from_attr.attrelid = COALESCE(from_ad.adrelid, d.objid)
LEFT JOIN pg_class from_table
  ON from_table.oid = d.classid

-- The referenced object.
LEFT JOIN pg_attrdef to_ad
  ON d.refclassid = 'pg_attrdef'::regclass
  AND to_ad.oid = d.refobjid
LEFT JOIN pg_rewrite to_rw
  ON d.refclassid = 'pg_rewrite'::regclass
  AND to_rw.oid = d.refobjid
LEFT JOIN pg_class to_class
  ON to_class.oid = COALESCE(to_rw.ev_class, to_ad.adrelid, d.refobjid)
LEFT JOIN pg_type to_type
  ON d.refclassid = 'pg_type'::regclass
  AND to_type.oid = d.refobjid
LEFT JOIN pg_proc to_proc
  ON d.refclassid = 'pg_proc'::regclass
  AND to_proc.oid = d.refobjid
LEFT JOIN pg_extension to_ext
  ON d.refclassid = 'pg_extension'::regclass
  AND to_ext.oid = d.refobjid
LEFT JOIN pg_constraint to_con
  ON d.refclassid = 'pg_constraint'::regclass
  AND to_con.oid = d.refobjid
LEFT JOIN pg_attribute to_attr
  ON (d.refobjsubid <> 0 OR to_ad.adrelid IS NOT NULL)
  AND to_attr.attnum = COALESCE(to_ad.adnum, d.refobjsubid)
  AND to_attr.attrelid = COALESCE(to_ad.adrelid, d.refobjid)
LEFT JOIN pg_class to_table
  ON to_table.oid = d.refclassid

WHERE d.deptype NOT IN ('i', 'a')
  AND COALESCE(to_class.relnamespace, to_type.typnamespace, to_proc.pronamespace, to_ext.extnamespace, to_con.connamespace) = 'public'::regnamespace
  AND COALESCE(from_class.relnamespace, from_type.typnamespace, from_proc.pronamespace) = 'public'::regnamespace