背景
在 PostgreSQL 中删除(或替换)对象时,如果存在依赖关系,则删除将失败(不指定CASCADE
).
Problem
数据库返回的错误信息没有列出依赖对象。
示例解决方案
该查询可能类似于:
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
不见了。
Related
- http://postgresql.1045698.n5.nabble.com/information-schema-problem-td2144069.html http://postgresql.1045698.n5.nabble.com/information-schema-problem-td2144069.html
- http://www.alberton.info/postgresql_meta_info.html http://www.alberton.info/postgresql_meta_info.html
Question
如何按名称和类型生成依赖对象的列表?
建议的解决方案对我的 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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)