查找表或视图的依赖对象 [英] Find dependent objects for a table or view

问题描述

在PostgreSQL中删除(或替换)对象时，如果存在依赖关系，则删除将失败(不指定CASCADE).

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

数据库返回的错误消息未列出相关对象.

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

查询可能类似于:

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';

objid丢失.

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

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

如何通过名称和类型生成依赖对象的列表?

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

推荐答案

建议的解决方案不适用于Postgresql 9.1.4

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

这有效:

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;

这篇关于查找表或视图的依赖对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！