PostgreSQL - 所有表的分层列表

Question

我可以帮助您翻译相关的IT技术内容，您需要列出PostgreSQL数据库中所有表格并按层次顺序排列。也就是说，如果“用户”表格有一个指向“角色”表格的外键，则它必须在“角色”表格之后列出。以下是等同于您要求的内容：

EXEC sp_msdependencies @intrans = 1 

在SQL Server中。

我尝试过这个，但没有成功：

SELECT
    pt.table_name as tablename,
    string_agg(DISTINCT ccu.table_name, ',') AS reftable
FROM information_schema.tables pt
LEFT JOIN information_schema.columns c
    ON c.table_name = pt.table_name
LEFT JOIN information_schema.table_constraints tc
    ON tc.table_name = pt.table_name AND tc.constraint_type = 'FOREIGN KEY'
LEFT JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name AND kcu.column_name = c.column_name
LEFT JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE pt.table_schema = 'public'
GROUP BY pt.table_name,pt.table_type
ORDER BY pt.table_type DESC, COUNT(TRUE) ASC;

- Marcelo Rodovalho

Answer 1

我认为你不能用简单的SELECT来完成这个任务，你可能需要使用递归查询。 information_schema对于这种情况似乎特别不适用，因为它假设约束名在模式内是唯一的，而Postgres并没有强制执行这一点。换句话说，如果你有两个具有相同名称的约束，我看不出在constraint_column_usage中如何区分它们。所以最好使用Postgres自己的目录。

这似乎可以工作，尽管我没有进行彻底的测试：

WITH RECURSIVE ref (tbl, reftbl, depth) AS (
  SELECT pg_class.oid, NULL::oid, 0
  FROM pg_class
  JOIN pg_namespace ON
    pg_namespace.oid = pg_class.relnamespace
  WHERE 
    relkind = 'r' AND
    nspname = 'public' AND
    NOT EXISTS (
      SELECT 1 FROM pg_constraint
      WHERE 
        conrelid = pg_class.oid AND
        contype = 'f'
    )
  UNION ALL
  SELECT conrelid, ref.tbl, ref.depth + 1
  FROM ref
  JOIN pg_constraint ON
    confrelid = ref.tbl AND
    contype = 'f'
)
SELECT
  tbl::regclass::text as tablename,
  string_agg(DISTINCT reftbl::regclass::text, ',') as reftables
FROM ref
GROUP BY tablename
ORDER BY max(depth)

- Nick Barnes

---

哦，谢谢你的回答。但是你的例子没有返回任何东西，只是一直显示“查询正在运行”，没有结束。 :D - Marcelo Rodovalho

@Marcelo：你有循环引用吗？如果是这样，则无法对其进行排序。 - Nick Barnes