🏆 文章目标：整理PostgreSQL 递归查询的方式，方便自己和大家快速查看。
🍀 PostgreSQL 递归查询（含层级和结构）
✅ 创作者：Jay…
🎉 个人主页：Jay的个人主页
🍁 展望：若本篇讲解内容帮助到您，请帮忙点个赞吧，您的支持是我继续写作的最大动力，谢谢。🙏

背景

父子关系的表中，避免不了相关正向查询，和反向查询的业务逻辑。

• 根据已知的“父对象”，递归查询所有的子级对象。
• 根据已知的“子对象”，递归查询所有的父级对象。
• 根据已知的“子对象/父对象”，递归查询所有的父级对象/子对象，并带有层级和结构。

对于第三点业务需求，PostgreSQL默认没有类似于Oracle的关键字“Level”，如果需要获取层级，需要自行构建，或者通过安装cross这个脚本来进行扩展（cross脚本处于安装目录，具体位置自行百度）。

实践

准备数据

创建表

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

插入数据

insert into city values('002' , 0 , '浙江省'); 
insert into city values('001' , 0 , '广东省'); 
insert into city values('003' , '002' , '衢州市');  
insert into city values('004' , '002' , '杭州市') ; 
insert into city values('005' , '002' , '湖州市');  
insert into city values('006' , '002' , '嘉兴市') ; 
insert into city values('007' , '002' , '宁波市');  
insert into city values('008' , '002' , '绍兴市') ; 
insert into city values('009' , '002' , '台州市');  
insert into city values('010' , '002' , '温州市') ; 
insert into city values('011' , '002' , '丽水市');  
insert into city values('012' , '002' , '金华市') ; 
insert into city values('013' , '002' , '舟山市'); 
insert into city values('014' , '004' , '上城区') ; 
insert into city values('015' , '004' , '下城区');  
insert into city values('016' , '004' , '拱墅区') ; 
insert into city values('017' , '004' , '余杭区') ; 
insert into city values('018' , '011' , '金东区') ; 
insert into city values('019' , '001' , '广州市') ; 
insert into city values('020' , '001' , '深圳市') ;
insert into city values('021' , '013' , '普陀区');  

业务实践

根据已知的“父对象” - > “浙江省”，递归查询所有的子级对象。

SQL:

WITH RECURSIVE cte AS 
( 
SELECT id, name, pid FROM city WHERE id = '002'
UNION ALL  
SELECT child.id, child.name, child.pid FROM city child INNER JOIN cte parent ON parent.id = child.pid 
)
SELECT * FROM cte

结果：

根据已知的“子对象” - “普陀区”，递归查询所有的父级对象。

SQL：

WITH RECURSIVE cte AS 
( 
SELECT id, name, pid FROM city WHERE id = '021'
UNION ALL  
SELECT parent.id, parent.name, parent.pid FROM city parent INNER JOIN cte child ON parent.id = child.pid 
)
SELECT * FROM cte

结果：

根据已知的“子对象” - “普陀区”，递归查询所有的父级对象，并带有层级和结构。

SQL:

WITH RECURSIVE cte AS 
( 
SELECT id, name, pid, name::varchar(150) as combined_name, id::varchar(150) AS combined_id, 1 AS LEVEL 
FROM city WHERE id = '021'

UNION ALL
  
SELECT parent.id, parent.name, parent.pid, (child.combined_name || '>' || parent.name)::varchar(150) AS combined_name, 
(child.combined_id || '>' || parent.id)::varchar(150) AS combined_id, child.LEVEL + 1 AS LEVEL 
FROM city parent INNER JOIN cte child ON parent.id = child.pid 
)
SELECT * FROM cte

结果：

跨层级反查时，可以考虑利用组合的id或者名称作为入口。

FAQ

1、ERROR: recursive query “t” column 2 has type character varying(150) in non-recursive term but type character varying overall

针对拼接的字段，需要重新定义其数据类型及长度。参考如下：