前言：Ora2pg处理递归查询并不太好使，特别是当递归查询作为子查询存在，且引用外层 表字段时，递归查询在pg里的语法是with recursive，写法不如oracle简洁，下面介绍转换语法：

1. 测试表结构

create table trees
(
  id  int,
  code  int,--本节点编号
  pcode int,--父节点编号
  info text
); 

-- 插入测试数据
insert into trees values(1,100,null,'course A');
insert into trees values(2,10010,100,'course A10');
insert into trees values(3,1001010,10010,'course A1010');
insert into trees values(4,1001011,10010,'course A1011');
insert into trees values(5,10011,100,'course A11');
insert into trees values(6,10012,100,'course A12');
insert into trees values(7,200,null,'course B');
insert into trees values(8,300,null,'course C');

2. 查询节点编号10010本级节点下级节点

-- oracle
select * 
from trees t
start with t.code = 10010
connect by prior t.code = pcode  ;

-- pg
with recursive cte as(
    select x.* 
    from trees x
    where x.code = 10010
    union all 
    select y.*
    from trees y
    join cte c on c.code = y.pcode
)select * from cte;

3. 查询节点编号10010本级节点上级节点

-- oracle
select * 
from trees t
start with t.code = 10010
connect by  t.code = prior pcode ;

-- pg
with recursive cte as(
    select x.* 
    from trees x
    where x.code = 10010
    union all 
    select y.*
    from trees y
    join cte c on c.pcode = y.code 
)select * from cte;

4. connect by level替换

-- oracle
SELECT REGEXP_SUBSTR(nn, '[^,]+', 1, LEVEL) AS AM 
  FROM insertmp  
  CONNECT BY LEVEL <= LENGTH(nn)-LENGTH(REPLACE(nn, ','))+1

-- pg
 select distinct regexp_split_to_table(nn,',') am from insertmp;

5. 计算300到500之间的偶数平均值

--不使用递归
with cet1 as(
  select generate_series(300,500) as a
),cet2 as(
select a from cet1 where a%2=0
)select  avg(a) from cet2;

--使用递归
with recursive t(n) as(
   values(300)
    union all 
    select n+2 from t where n<500 
)select avg(n) from t;

6. 多个递归调用例子

格式如下：

WITH RECURSIVE
  cte1 AS (...)         -- 可以为非递归语句
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- 递归语句
, cte3 AS (...)         -- 递归语句
SELECT ... FROM cte3 WHERE ...

注意：
1.有混合递归和非递归，都统一使用WITH RECURSIVE。
2.顺序问题，先写非递归语句，然后写递归语句。

WITH RECURSIVE
  cte1 AS (select x.* 
    from trees x
    where x.code = 10010
    union all 
    select y.*
    from trees y
    join cte1 c on c.code = y.pcode)
, cte2 AS (    select x.* 
    from trees x
    where x.code = 10010
    union all 
    select y.*
    from trees y
    join cte2 c on c.pcode = y.code )
SELECT * FROM cte1 union all select * from cte2;

往期回顾

• PG实践学习笔记
• PostgreSQL源码编译dblink
• PostgreSQL11及以上集成orafce

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴加我微信进群吹牛唠嗑，交流技术，互赞文章。