如何使用WITH RECURSIVE和REGEXP_SUBSTR将Oracle代码迁移至PostgreSQL？

一、核心语法映射 #

Oracle的层级查询（CONNECT BY）和字符串拆分逻辑，在PostgreSQL中可通过WITH RECURSIVE递归CTE结合REGEXP_SUBSTR实现，核心对应关系：

• Oracle CONNECT BY → PostgreSQL WITH RECURSIVE：递归CTE包含锚点查询（初始数据）和递归查询（引用自身循环处理）。
• Oracle REGEXP_SUBSTR → PostgreSQL REGEXP_SUBSTR：参数顺序基本兼容，PostgreSQL多了可选的flags参数，不影响基础拆分场景。
• 字符串拆分逻辑：Oracle用CONNECT BY INSTR(...) > 0循环提取子串，PostgreSQL用递归CTE逐次递增occurrence参数，直到提取结果为空。

二、给定Oracle代码的PostgreSQL移植（最小改动版） #

原Oracle代码 #

SELECT pl.rd,
       pl.rs,
       pl.agency_plan,
       pl.o__id
FROM ALIS.PLAF pl
WHERE RD BETWEEN to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy') AND last_day(to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy'))
  AND RS IN (SELECT TO_CHAR(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) RS
             FROM (SELECT TO_CHAR(P_RS_LIST) str FROM dual) t 
             CONNECT BY INSTR(str, ',', 1, LEVEL - 1) > 0);

移植后的PostgreSQL代码（递归CTE版） #

SELECT pl.rd,
       pl.rs,
       pl.agency_plan,
       pl.o__id
FROM ALIS.PLAF pl
WHERE RD BETWEEN to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy') 
              AND (date_trunc('month', to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy')) + interval '1 month')::date - 1
  AND RS IN (
    WITH RECURSIVE split_rs AS (
      SELECT 1 AS level, 
             REGEXP_SUBSTR(P_RS_LIST, '[^,]+', 1, 1) AS rs,
             P_RS_LIST AS str
      WHERE P_RS_LIST IS NOT NULL AND P_RS_LIST <> ''
      UNION ALL
      SELECT level + 1,
             REGEXP_SUBSTR(str, '[^,]+', 1, level + 1),
             str
      FROM split_rs
      WHERE REGEXP_SUBSTR(str, '[^,]+', 1, level + 1) IS NOT NULL
    )
    SELECT rs FROM split_rs
);

关键改动说明 #

1. 日期函数替换：PostgreSQL无last_day函数，用date_trunc获取当月首日，加1个月后减1天实现当月最后一天的计算。
2. 移除dual表：PostgreSQL无需从dual查询常量/变量，直接引用P_RS_LIST即可。
3. CONNECT BY转递归CTE：用WITH RECURSIVE创建递归逻辑，锚点提取第一个子串，递归成员逐次提取下一个子串，直到无结果时终止。
4. 简化类型转换：若P_RS_LIST本身为字符串类型，可省略TO_CHAR转换（保留也不影响）。

更简洁的替代方案（PostgreSQL 14+） #

如果使用PostgreSQL 14及以上版本，可利用STRING_TO_ARRAY+UNNEST直接拆分字符串，进一步减少代码量：

SELECT pl.rd,
       pl.rs,
       pl.agency_plan,
       pl.o__id
FROM ALIS.PLAF pl
WHERE RD BETWEEN to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy') 
              AND (date_trunc('month', to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy')) + interval '1 month')::date - 1
  AND RS IN (SELECT UNNEST(STRING_TO_ARRAY(P_RS_LIST, ',')));

这个方案完全替代了递归CTE和REGEXP_SUBSTR，代码更紧凑，符合PostgreSQL的原生语法风格。

内容的提问来源于stack exchange，提问作者Ekz0