PostgreSQL 12.2递归语法错误：Ora2Pg迁移Oracle代码遇问题

看起来Ora2Pg这次的转换没做好，生成的PostgreSQL代码语法完全混乱了，我来帮你修复这个问题，顺便解释下正确的写法。

首先先明确你原Oracle代码的核心作用：通过CONNECT BY递归拆分逗号分隔的p_site参数，逐个取出每个非逗号分隔的站点值，然后循环处理每个站点。

Ora2Pg生成的代码错误百出——它试图把Oracle的递归逻辑硬套进PostgreSQL的递归CTE，但不仅误用了Oracle专属的level伪列，还把递归CTE的结构搞乱了，最终导致PostgreSQL解析时抛出"missing 'LOOP' at end of SQL expression"的错误。

正确的PostgreSQL替代写法 #

PostgreSQL提供了更简洁高效的字符串拆分函数，完全不需要复杂的递归CTE就能实现相同的功能，这里推荐两种和原Oracle逻辑完全一致的写法：

方法1：使用regexp_matches（最贴近原Oracle行为）

这个方法会直接提取所有非逗号分隔的元素，自动跳过空值（和原Oracle的regexp_substr逻辑完全匹配）：

FOR rec_site IN (
    SELECT elem AS site
    FROM regexp_matches(p_site, '[^,]+', 'g') AS matches(elem)
) LOOP
    -- 在这里写你的循环处理逻辑，比如：
    -- RAISE NOTICE 'Processing site: %', rec_site.site;
END LOOP;

方法2：使用regexp_split_to_table（更简洁）

如果你的p_site不会出现连续逗号或首尾逗号的情况，可以直接用这个函数：

FOR rec_site IN (
    SELECT regexp_split_to_table(p_site, ',') AS site
) LOOP
    -- 处理逻辑
END LOOP;

要是需要严格匹配原Oracle跳过空元素的行为，只需调整正则表达式即可：

FOR rec_site IN (
    SELECT regexp_split_to_table(p_site, '[^,]+', 'g') AS site
) LOOP
    -- 处理逻辑
END LOOP;

为什么原转换代码出错？ #

原生成的递归CTE犯了几个关键错误：

• PostgreSQL中没有level伪列，递归CTE需要手动维护计数器（比如添加counter字段）
• 递归CTE的结构不符合规范：必须包含初始查询和递归查询两部分，用UNION ALL连接，且递归查询需要引用CTE自身
• 代码里存在冗余的重复子查询和无效的JOIN，完全不符合PostgreSQL的语法规则

用内置函数替代递归CTE不仅能避免这些语法问题，还能让代码更易读、性能更优。

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