查找表或视图的依赖对象

本文介绍了查找表或视图的依赖对象的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在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丢失.

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

• postgresql.1045698.n5.nabble/information-schema-problem-td2144069.html
• 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;