[PostgreSQL]获取表之前的依赖关系

获取表之前的依赖关系

 1 SELECT DISTINCT
 2     c2.relname AS "表名"
 3     , CASE c2.relkind 
 4         WHEN 'r' THEN 'table' 
 5         WHEN 'v' THEN 'view' 
 6         ELSE '' 
 7         END AS "表类型"
 8     , c1.relname AS "被参照表名"
 9     , CASE c1.relkind 
10         WHEN 'r' THEN 'table' 
11         WHEN 'v' THEN 'view' 
12         ELSE '' 
13         END AS "被参照表类型" 
14 FROM
15     pg_depend d 
16     INNER JOIN pg_class c1 
17         ON d.refobjid = c1.oid 
18         AND c1.relkind IN ('r', 'v')            --r=table, v=view
19         AND c1.relnamespace = ( 
20             SELECT
21                 oid 
22             FROM
23                 pg_namespace 
24             WHERE
25                 nspname = 'public'              -- schema名
26         ) 
27         AND d.refobjsubid != 0 
28     INNER JOIN pg_rewrite r 
29         ON d.objid = r.oid 
30     INNER JOIN pg_class c2 
31         ON r.ev_class = c2.oid 
32         AND c2.relkind IN ('r', 'v')            --r=table, v=view
33 ORDER BY
34     1
35     , 3

 

posted on 2024-07-16 10:30  Osiris4Net  阅读(2)  评论(0)  编辑  收藏  举报

导航