The longest referential paths

Copy SQL

WITH RECURSIVE transitive_closure AS (
WITH foo AS (SELECT o.conrelid AS child, nl.nspname AS child_table_schema, cl.relname AS child_table, 
o.confrelid AS parent, nf.nspname AS parent_table_schema, cf.relname AS parent_table
FROM pg_constraint AS o INNER JOIN pg_class AS cl ON o.conrelid=cl.oid
INNER JOIN pg_namespace AS nl ON cl.relnamespace=nl.oid
INNER JOIN pg_class AS cf ON o.confrelid=cf.oid
INNER JOIN pg_namespace AS nf ON cf.relnamespace=nf.oid
WHERE o.contype='f')
SELECT child, parent, child_table_schema, child_table, parent_table_schema, parent_table, 1 AS depth, 
child || '.' || parent || '.' AS path_string,
child_table_schema || '.' || child_table || '/' || parent_table_schema || '.' || parent_table || '/' AS path_string_long
FROM foo
UNION ALL
SELECT tc.child, e.parent, tc.child_table_schema, tc.child_table, e.parent_table_schema, e.parent_table, tc.depth + 1,  
tc.path_string || e.parent || '.' AS path_string,
tc.path_string_long || e.parent_table_schema|| '.' || e.parent_table || '/' AS path_string_long
FROM foo AS e INNER JOIN transitive_closure AS tc  ON e.child = tc.parent
WHERE tc.path_string NOT LIKE '%' || e.parent || '.%'),
paths AS (SELECT child_table_schema, child_table, parent_table_schema, parent_table, depth, rtrim(path_string_long,'.') AS path
FROM transitive_closure)
SELECT parent_table_schema, parent_table, child_table_schema, child_table, depth, path
FROM paths
WHERE depth=(SELECT Max(depth) AS m
FROM paths)
ORDER BY parent_table_schema, parent_table, child_table_schema, child_table;