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 AS e INNER JOIN transitive_closure AS tc  ON e.child = tc.parent
WHERE tc.path_string NOT LIKE '%' || e.parent || '.%'),
biggest_distance AS (SELECT child, Max(distance) AS depth
FROM transitive_closure
GROUP BY child)
SELECT nc.nspname AS table_schema, c.relname AS table_name, depth
FROM biggest_distance AS o INNER JOIN pg_class AS c ON o.child=c.oid
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')
UNION SELECT nc.nspname AS table_schema, c.relname AS table_name, 0 AS depth
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.conrelid=c.oid)
ORDER BY depth DESC, table_schema, table_name;