Oracle SQL转PostgreSQL SQL：求兼容双数据库的通用代码方案

PostgreSQL 专属版本 #

直接替换Oracle特有的递归语法和rownum：

WITH RECURSIVE recursive_other AS (
    -- 递归起始节点：对应原START WITH条件
    SELECT o.* 
    FROM other o 
    WHERE o.vnum = ?
    UNION ALL
    -- 递归关联：对应原CONNECT BY PRIOR o.onum = o.vnum
    SELECT o.* 
    FROM other o
    JOIN recursive_other ro ON o.vnum = ro.onum
)
SELECT 
    row_number() OVER () AS rownum,
    p.apple, c.apple, o.boy, o.cat, o.bat, d.goat, v.emp
FROM recursive_other o
INNER JOIN cast c ON c.id = o.id
INNER JOIN dept d ON c.oid = d.oid
LEFT JOIN posh p ON p.pnum = o.onum
LEFT JOIN valent v ON v.vnum = o.onum
ORDER BY rownum DESC;

跨Oracle/PostgreSQL兼容版本 #

由于两种数据库的递归语法差异较大，需通过条件分支实现兼容（依赖支持条件编译的SQL工具或应用层模板替换）：

-- Oracle 分支
/*+IF ORACLE*/
SELECT rownum,
    p.apple,c.apple,o.boy,o.cat,o.bat,d.goat,v.emp
FROM other o
INNER JOIN cast c ON c.id=o.id
INNER JOIN dept d ON c.oid=d.oid
LEFT JOIN posh p ON p.pnum=o.onum
LEFT JOIN valent v ON v.vnum=o.onum
START WITH o.vnum = ? CONNECT BY PRIOR o.onum = o.vnum
ORDER BY rownum desc;
/*+ENDIF*/

-- PostgreSQL 分支
/*+IF POSTGRESQL*/
WITH RECURSIVE recursive_other AS (
    SELECT o.* 
    FROM other o 
    WHERE o.vnum = ?
    UNION ALL
    SELECT o.* 
    FROM other o
    JOIN recursive_other ro ON o.vnum = ro.onum
)
SELECT 
    row_number() OVER () AS rownum,
    p.apple, c.apple, o.boy, o.cat, o.bat, d.goat, v.emp
FROM recursive_other o
INNER JOIN cast c ON c.id = o.id
INNER JOIN dept d ON c.oid = d.oid
LEFT JOIN posh p ON p.pnum = o.onum
LEFT JOIN valent v ON v.vnum = o.onum
ORDER BY rownum DESC;
/*+ENDIF*/

关键差异说明 #

• 行号生成：Oracle的rownum是查询结果的原生行号，PostgreSQL用row_number() OVER ()模拟相同效果。
• 递归查询：Oracle的START WITH ... CONNECT BY层级查询语法，在PostgreSQL中需用WITH RECURSIVE递归CTE实现，逻辑上保持一致：先取起始节点，再递归关联父节点的onum与子节点的vnum。

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