Tree Recursive
初始化 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;
27 January 2026