Recursive CTE — Walk a Hierarchy

-- Every descendant of employee 42 in the org chart
WITH RECURSIVE reports AS (
    -- Anchor row: the starting employee
    SELECT id, name, manager_id, 0 AS depth
    FROM   employees
    WHERE  id = 42

    UNION ALL

    -- Recursive part: anyone whose manager is already in `reports`
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM   employees e
    JOIN   reports   r ON e.manager_id = r.id
)
SELECT id, name, depth FROM reports ORDER BY depth, name;

-- Walk a comment tree (parent_id → children)
WITH RECURSIVE thread AS (
    SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
    FROM   comments WHERE id = $1
    UNION ALL
    SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
    FROM   comments c
    JOIN   thread t ON c.parent_id = t.id
)
SELECT REPEAT('  ', depth) || body AS indented, path
FROM   thread
ORDER  BY path;