如何在Redshift中为对象创建依赖关系列表？

Question

如何在Redshift中为对象创建依赖关系列表？

17

我的团队采用了层层嵌套的视图，因此经常使用DROP TABLE CASCADE会导致灾难性后果和很多的试错。

我想要的是一条查询语句，能够返回给定schema和table的所有依赖对象，并按正确的顺序重新创建它们，这样它们就可以被自动化并在脚本中运行。我正在使用修改版的Redshift DROP TABLE文档上的依赖性查询http://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html。

它似乎只返回视图及其依赖关系，而不是普通表。我感觉离成功不远了，我漏掉了什么？

WITH dependencies AS (
    SELECT DISTINCT
      cls1.oid     AS tbloid,
      nsp1.nspname AS schemaname,
      cls1.relname AS name,
      nsp2.nspname AS refbyschemaname,
      cls2.relname AS refbyname,
      cls2.oid     AS viewoid
    FROM pg_catalog.pg_class cls1
      JOIN pg_catalog.pg_depend dep1
        ON cls1.relfilenode = dep1.refobjid
      JOIN pg_catalog.pg_depend dep2
        ON dep1.objid = dep2.objid
      JOIN pg_catalog.pg_class cls2
        ON dep2.refobjid = cls2.relfilenode
      LEFT OUTER JOIN pg_namespace nsp1
        ON cls1.relnamespace = nsp1.oid
      LEFT OUTER JOIN pg_namespace nsp2
        ON cls2.relnamespace = nsp2.oid
    WHERE dep2.deptype IN ('i' :: "CHAR", 'n' :: "CHAR")
          AND cls2.relkind IN ('v' :: "CHAR", 'r' :: "CHAR")
          AND nsp1.nspname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY 4, 5
),

    joined_to_views AS (
      SELECT
        d.schemaname,
        d.name,
        d.refbyschemaname,
        d.refbyname,
        p.definition
      FROM dependencies d
        LEFT JOIN pg_views p
          ON d.refbyschemaname = p.schemaname AND d.refbyname = p.viewname
  )

SELECT *
FROM joined_to_views

- flybonzai

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rahul Gupta · Accepted Answer

这对你有用吗？

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;

如果我做出了错误的假设，请在评论中指出，我会重新调整我的答案。