sql-paths-for-daxim

with recursive
dag (vertex, successor) as (values
    ('alpha', 'bravo'),
    ('bravo', 'charlie'),
    ('charlie', 'delta'),
    ('delta', null),
    ('alpha', 'echo'),
    ('echo', 'foxtrot'),
    ('bravo', 'foxtrot'),
    ('foxtrot', null),
    ('alpha', 'golf'),
    ('golf', 'hotel'),
    ('hotel', null)
),
head (vertex) as (
        select vertex from dag except select successor from dag
),
paths (row, depth, parent_row, vertex, successor) as (
        select row_number() over (), 1, 0::bigint, dag.vertex, dag.successor
                from head
                join dag on (dag.vertex = head.vertex)
        union all
        select row_number() over (), depth + 1, row, dag.vertex, dag.successor
                from paths
                join dag on (dag.vertex = paths.successor)
),
result (path_id, parent_row, depth, vertex) as (
        select row_number() over (), parent_row, depth, vertex
                from paths
                where successor is null
        union all
        select result.path_id, paths.parent_row, paths.depth, paths.vertex
                from result
                join paths on (paths.row = result.parent_row and paths.depth = result.depth - 1)
)
select path_id, depth, vertex
        from result
        order by 1, 2
;