Tree Recursive

Warning

没有考虑环的问题

初始化 tmp_tree_node

drop table if exists tmp_tree_node;
create table tmp_tree_node
(
    id   int primary key,
    pid  int,
    name varchar(100)
);

insert into tmp_tree_node
values
    (1, -1, 'src'),
    (10, 1, 'main'),
    (101, 10, 'java'),
    (1001, 101, 'HelloWorld.java'),
    (102, 10, 'resources'),
    (1002, 102, 'application.yml'),
    (20, 1, 'test'),
    (201, 20, 'java'),
    (2001, 201, 'HelloWorldTest.java'),
    (202, 20, 'resource'),
    (2002, 201, 'application.properties');

查询子节点

-- query children
with recursive cte (id, pid, level, name, name_list, path) as (
    select id,
           pid,
           1                                       as level,
           name,
           array_append(array []::varchar[], name) as name_list,
           name::text                              as path
    from tmp_tree_node
    where pid = -1
    union all
    select c.id,
           c.pid,
           p.level + 1,
           c.name,
           p.name_list || array_append(array []::varchar[], c.name) as name_list,
           concat(p.path, '/', c.name) ::text                       as path
    from tmp_tree_node c
             inner join cte as p
                        on c.pid = p.id)
select *
from cte;

查询父节点

-- query parent
with recursive cte (id, pid, len, name, name_list) as (
    select id,
           pid,
           1                                       as len,
           name,
           array_append(array []::varchar[], name) as name_list
    from tmp_tree_node
    where id = 2002

    union all

    select p.id,
           p.pid,
           c.len + 1                                                as len,
           p.name,
           c.name_list || array_append(array []::varchar[], p.name) as name_list
    from tmp_tree_node p
             inner join cte c on c.pid = p.id)
select *
from cte;