---

SELECT conrelid::regclass AS child_table, conname,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE confrelid = 'scan'::regclass AND confdeltype = 'c';


WITH RECURSIVE tree AS (
  SELECT conrelid, confrelid, conname, 1 AS depth
  FROM pg_constraint
  WHERE confrelid = 'scan'::regclass AND contype = 'f' AND confdeltype = 'c'
  UNION ALL
  SELECT c.conrelid, c.confrelid, c.conname, t.depth + 1
  FROM pg_constraint c
  JOIN tree t ON c.confrelid = t.conrelid
  WHERE c.contype = 'f' AND c.confdeltype = 'c' AND t.depth < 10
)
SELECT depth, conrelid::regclass AS table_name,
       confrelid::regclass AS parent_table, conname
FROM tree ORDER BY depth, table_name;