sql.sql

create table tree(
    id text primary key,
    parent_id text null
);

insert into tree values
    ('a', null),
    ('b', 'a'),
    ('c', 'a'),
    ('d', 'b'),
    ('e', 'b'),
    ('f', 'c'),
    ('g', 'c');

prepare get_branch as
with recursive sub as (
    select tree.*, 0 as level from tree where id = $1
    UNION ALL
    select tree.*, sub.level + 1
        from tree, sub
    where tree.parent_id = sub.id
)
select * from sub order by level;

execute get_branch('b');

┌────┬───────────┬───────┐
│ id │ parent_id │ level │
├────┼───────────┼───────┤
│ b  │ a         │     0 │
│ d  │ b         │     1 │
│ e  │ b         │     1 │
└────┴───────────┴───────┘

prepare get_path as
with recursive sub as (
    select tree.*, 0 as level from tree where id = $1
    union all
    select tree.*, sub.level - 1
        from tree, sub
    where tree.id = sub.parent_id
)
select * from sub order by level;

execute get_path('b');

┌────┬───────────┬───────┐
│ id │ parent_id │ level │
├────┼───────────┼───────┤
│ a  │ <null>    │    -1 │
│ b  │ a         │     0 │
└────┴───────────┴───────┘

execute get_path('f');
┌────┬───────────┬───────┐
│ id │ parent_id │ level │
├────┼───────────┼───────┤
│ a  │ <null>    │    -2 │
│ c  │ a         │    -1 │
│ f  │ c         │     0 │
└────┴───────────┴───────┘