sql-paths-for-daxim-pgsql

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 (path, successor) as (
        select array[dag.vertex], dag.successor
                from head
                join dag on (dag.vertex = head.vertex)
        union all
        select array_append(paths.path, dag.vertex), dag.successor
                from paths
                join dag on (dag.vertex = paths.successor)
),
result_paths (path_id, path) as (
        select row_number() over (), path
                from paths
                where successor is null
),
result (path_id, depth, vertex) as (
        select path_id, p.nr, p.elem
                from result_paths
                left join lateral unnest (path) with ordinality as p(elem, nr) on true
)
select * from result order by 1,2;