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

最新推荐文章于 2024-07-24 16:56:50 发布

Osiris_Syou

最新推荐文章于 2024-07-24 16:56:50 发布

阅读量85

点赞数 2

文章标签： postgresql 服务器数据库

本文链接：https://blog.csdn.net/qkhhimkfrgw/article/details/140499168

版权

获取表之前的依赖关系

SELECT DISTINCT
    c2.relname AS "表名"
    , CASE c2.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        ELSE ''
        END AS "表类型"
    , c1.relname AS "被参照表名"
    , CASE c1.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        ELSE ''
        END AS "被参照表类型"
FROM
    pg_depend d
    INNER JOIN pg_class c1
        ON d.refobjid = c1.oid
        AND c1.relkind IN ('r', 'v')            --r=table, v=view
        AND c1.relnamespace = (
            SELECT
                oid
            FROM
                pg_namespace
            WHERE
                nspname = 'public'              -- schema名
        )
        AND d.refobjsubid != 0
    INNER JOIN pg_rewrite r
        ON d.objid = r.oid
    INNER JOIN pg_class c2
        ON r.ev_class = c2.oid
        AND c2.relkind IN ('r', 'v')            --r=table, v=view
ORDER BY
    1
    , 3