php - PostgreSQL-所有表的分层列表

我需要按层次顺序列出我的数据库（PostgreSQL）的所有表。
也就是说：如果表“user”有表“role”的外键，那么它必须在列表中的表“role”之后。
相当于：

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;

最佳答案

我不认为你能用一个简单的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)