如何将Oracle的CONNECT BY查询转换为PostgreSQL递归WITH查询？

转换后的PostgreSQL查询 #

WITH RECURSIVE offer_hierarchy AS (
    -- 初始数据集：匹配Oracle START WITH的根节点及其直接子节点
    SELECT
        po1.product_offer_name AS root_offer_name,
        poo.child_product_offer_id AS product_offer_id,
        poo.product_offer_id AS parent_id
    FROM product_offer_offer poo
    JOIN product_offer po1 ON po1.product_offer_id = poo.product_offer_id
    JOIN product_offer po ON po.product_offer_id = poo.child_product_offer_id
    WHERE poo.product_offer_id IN (
        SELECT product_offer_id
        FROM product_offer
        WHERE CURRENT_TIMESTAMP BETWEEN available_start_date AND available_end_date
          AND product_id IS NOT NULL
          AND (:TopLevelOffer = 0 OR product_offer_id = :TopLevelOffer)
    )
    -- 递归遍历：逐层获取子节点的下级节点，对应Oracle CONNECT BY逻辑
    UNION ALL
    SELECT
        cte.root_offer_name,
        poo.child_product_offer_id AS product_offer_id,
        poo.product_offer_id AS parent_id
    FROM offer_hierarchy cte
    JOIN product_offer_offer poo ON poo.product_offer_id = cte.product_offer_id
    JOIN product_offer po ON po.product_offer_id = poo.child_product_offer_id
)
-- 筛选叶子节点并去重，对应Oracle connect_by_isleaf = 1
SELECT DISTINCT
    root_offer_name AS Offer,
    product_offer_id
FROM offer_hierarchy oh
WHERE NOT EXISTS (
    SELECT 1
    FROM product_offer_offer poo
    WHERE poo.product_offer_id = oh.product_offer_id
);

核心转换要点 #

1. 递归结构替换：用PostgreSQL的WITH RECURSIVE替代Oracle的CONNECT BY，分为初始查询块（对应START WITH）和递归迭代块。
2. 根节点字段传递：递归过程中始终保留根节点的product_offer_name，等价于Oracle的connect_by_root(po1.product_offer_name)。
3. 叶子节点判断：原connect_by_isleaf = 1改为通过NOT EXISTS判断当前节点没有下级子节点（即不在product_offer_offer的product_offer_id字段中）。
4. 日期函数适配：Oracle的sysdate替换为PostgreSQL的CURRENT_TIMESTAMP（与NOW()功能一致）。
5. 变量兼容：原绑定变量:TopLevelOffer在PostgreSQL中可直接使用，无需修改语法。

内容的提问来源于stack exchange，提问作者KONDETI AJAY KUMAR