获取PostgreSQL中视图/表所依赖的表的列表 [英] Getting a list of tables that a view/table depends on in PostgreSQL

问题描述

在PostgreSQL中，是否有一种方法可以根据视图/表的外键使用和对给定表的访问来获取视图/表所依赖的所有表？

In PostgreSQL, is there a way to get all of the tables that a view/table depends on based on its use of foreign keys and access to a given table?

基本上，我希望能够使用脚本来复制视图/表的结构，并希望能够自动获取我也需要复制的表的列表，以使一切正常工作。

Basically, I want to be able to copy the structure of a view/table using a script and want to be able to automatically get the list of tables that I would also need to copy in order for everything to still work right.

此响应似乎朝着正确的方向发展，但并没有给我我期望/需要的结果。

This response appears to be headed in the right direction, but doesn't give me the results that I expect/need. Any suggestions?

推荐答案

使用来自Andy Lester的信息，我能够提出以下查询来检索我需要。

Using the info from Andy Lester, I was able to come up with the following queries to retrieve the information that I needed.

获取外键引用的表：

SELECT cl2.relname AS ref_table
FROM pg_constraint as co
JOIN pg_class AS cl1 ON co.conrelid=cl1.oid
JOIN pg_class AS cl2 ON co.confrelid=cl2.oid
WHERE co.contype='f' AND cl1.relname='TABLENAME'
ORDER BY cl2.relname;

从表中获取视图或规则所引用的表：

Get Tables that a View or Rules from a Table refer to:

SELECT cl_d.relname AS ref_table
FROM pg_rewrite AS r
JOIN pg_class AS cl_r ON r.ev_class=cl_r.oid
JOIN pg_depend AS d ON r.oid=d.objid
JOIN pg_class AS cl_d ON d.refobjid=cl_d.oid
WHERE cl_d.relkind IN ('r','v') AND cl_r.relname='TABLENAME'
GROUP BY cl_d.relname
ORDER BY cl_d.relname;

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