postgresql - Find dependent objects for a table or view

Question

Background

When dropping (or replacing) objects in PostgreSQL, if there are dependencies, the drop will fail (without specifying CASCADE).

Problem

The error message returned by the database does not list the dependent objects.

Example Solution

The query might look something like:

SELECT * FROM information_schema i, pg_depend pd WHERE
  i.object_id = pd.object_id AND
  i.object_type = 'TABLE' AND
  i.object_schema = 'public' AND
  i.object_name = 'table_with_dependents';

The objid is missing.

Related

• http://postgresql.1045698.n5.nabble.com/information-schema-problem-td2144069.html
• http://www.alberton.info/postgresql_meta_info.html

Question

How do you generate a list of dependent objects by name and type?

Answer 1

The suggested solution didn't work for me with postgresql 9.1.4

this worked:

SELECT dependent_ns.nspname as dependent_schema
, dependent_view.relname as dependent_view 
, source_ns.nspname as source_schema
, source_table.relname as source_table
, pg_attribute.attname as column_name
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid 
    AND pg_depend.refobjsubid = pg_attribute.attnum 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE 
source_ns.nspname = 'my_schema'
AND source_table.relname = 'my_table'
AND pg_attribute.attnum > 0 
AND pg_attribute.attname = 'my_column'
ORDER BY 1,2;