业务需求

BOM表，组织架构都是典型的树结构，多以父子形式存在数据库表中。
比如BOM展开，给定某个bom根，展开其所有的bom枝叶。
行政区划，给定某个省，展开其所有下级单位。

构造数据

create temp table tb(id varchar(3) , pid varchar(3) , name varchar(10)); 

insert into tb values('002' , 0 , '浙江省'); 
insert into tb values('001' , 0 , '广东省'); 
insert into tb values('003' , '002' , '衢州市');  
insert into tb values('004' , '002' , '杭州市') ; 
insert into tb values('005' , '002' , '湖州市');  
insert into tb values('006' , '002' , '嘉兴市') ; 
insert into tb values('007' , '002' , '宁波市');  
insert into tb values('008' , '002' , '绍兴市') ; 
insert into tb values('009' , '002' , '台州市');  
insert into tb values('010' , '002' , '温州市') ; 
insert into tb values('011' , '002' , '丽水市');  
insert into tb values('012' , '002' , '金华市') ; 
insert into tb values('013' , '002' , '舟山市');  
insert into tb values('014' , '004' , '上城区') ; 
insert into tb values('015' , '004' , '下城区');  
insert into tb values('016' , '004' , '拱墅区') ; 
insert into tb values('017' , '004' , '余杭区') ; 
insert into tb values('018' , '011' , '金东区') ; 
insert into tb values('019' , '001' , '广州市') ; 
insert into tb values('020' , '001' , '深圳市') ;
-- 预览数据
select * from tb;
-- 递归展开
with RECURSIVE cte as
(
select a.id,cast(a.name as varchar(100)) as fullname from tb a where id='002'
union all 
select k.id,cast(c.fullname||'>'||k.name as varchar(100)) as fullname  
from tb k 
inner join cte c on c.id = k.pid
)select id,fullname from cte ;

gp不支持RECURSIVE

5.4+开始支持，但需要打开配置项

show gp_recursive_cte_prototype
set gp_recursive_cte_prototype=on;
show gp_recursive_cte_prototype
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;