The number of direct and indirect child tables (foreign keys)

Copy SQL

WITH RECURSIVE transitive_closure AS (
WITH foo AS (SELECT conrelid AS child, confrelid AS parent
FROM pg_constraint
WHERE contype='f')
SELECT child, parent, 1 AS distance, child || '.' || parent || '.' AS path_string
FROM foo
UNION ALL
SELECT tc.child, e.parent, tc.distance + 1,  tc.path_string || e.parent || '.' AS path_string
FROM foo e INNER JOIN transitive_closure AS tc  ON e.child = tc.parent
WHERE tc.path_string NOT LIKE '%' || e.parent || '.%')
SELECT np.nspname AS table_schema, c.relname AS table_name, Count(*) AS number_of_all_children,
(SELECT Count(*) AS cnt FROM pg_constraint WHERE contype='f' AND pg_constraint.confrelid=c.oid) AS number_of_direct_children
FROM transitive_closure AS tc INNER JOIN pg_class AS c ON tc.parent=c.oid
INNER JOIN pg_namespace AS np ON c.relnamespace=np.oid
INNER JOIN pg_authid AS a ON np.nspowner=a.oid
WHERE (np.nspname='public' OR rolname<>'postgres')
GROUP BY c.oid, np.nspname, c.relname
UNION SELECT nc.nspname AS table_schema, c.relname AS table_name, 0 AS number_of_all_children, 0 AS number_of_direct_children
FROM pg_class AS c INNER JOIN pg_namespace AS nc ON c.relnamespace=nc.oid
INNER JOIN pg_authid AS a ON nc.nspowner=a.oid
WHERE (nc.nspname='public' OR rolname<>'postgres')
AND c.relkind='r'
AND NOT EXISTS (SELECT *
FROM pg_constraint
WHERE contype='f'
AND pg_constraint.confrelid=c.oid)
ORDER BY number_of_all_children DESC, table_schema, table_name;