postgresql中的树遍历查询

废话不多说直接coding
1、建表

CREATE TABLE test
(
  id text,
  pid text,
  msg text
)
insert into test(id,pid,msg) values('1','','一级（1）');
insert into test(id,pid,msg) values('2','','一级（2）');
insert into test(id,pid,msg) values('3','','一级（3）');
insert into test(id,pid,msg) values('4','','一级（4）');

insert into test(id,pid,msg) values('11','1','二级（1）');
insert into test(id,pid,msg) values('22','2','二级（2）');
insert into test(id,pid,msg) values('33','3','二级（3）');
insert into test(id,pid,msg) values('44','4','二级（4）');

insert into test(id,pid,msg) values('111','11','三级（1）');
insert into test(id,pid,msg) values('222','22','三级（2）');
insert into test(id,pid,msg) values('333','33','三级（3）');
insert into test(id,pid,msg) values('444','44','三级（4）');

2、查询

with recursive tmp as
(
  select a.id,a.pid,a.msg from test a where id='1'
  union all
  select a.id,a.pid,a.msg from test a inner join tmp t on t.id=a.pid
) select msg from tmp

结果：

"一级（1）"
"二级（1）"
"三级（1）"