SQL Server存储过程迁移至PostgreSQL时MERGE..USING..WHEN实现咨询

首先明确：你提到的「通过join搭配条件判断实现等价逻辑」是完全可行的，同时根据你使用的PostgreSQL版本，有两种成熟的实现路径，可1:1复刻原SQL的业务逻辑，不存在功能缺失。

原SQL业务逻辑拆解 #

先对齐原MERGE语句的三个核心分支，确保迁移后无逻辑偏差：

• 当源数据（解析后的phase列表）和目标表grow.plant_phase通过id_plant + id_phase匹配成功，且两边的date_started不一致时：更新目标表的date_started、date_updated、updated_by字段
• 当源数据在目标表中不存在匹配记录时：插入新记录，写入创建人、更新人字段
• 当目标表当前操作的id_plant下的记录，不在源数据列表中时：删除这部分冗余记录

实现方案一：PostgreSQL 15+ 原生MERGE语法适配 #

PostgreSQL 15版本开始正式支持原生MERGE语法，可最大程度对齐原SQL Server的写法，仅需替换SQL Server专属的语法和函数：

• 替换OPENJSON逻辑为PostgreSQL原生的jsonb_array_elements解析JSON入参
• 替换getutcdate()为(now() AT TIME ZONE 'UTC')，完全对齐原函数返回UTC时间的语义
• 其余WHEN分支逻辑完全保留原判断条件

适配后的代码示例：

WITH phase_list AS (
    SELECT :id_plant AS id_plant,
           (elem ->> 'id_phase')::INT AS id_phase,
           (elem ->> 'date_started')::DATE AS date_started
    FROM jsonb_array_elements(:phase_list::jsonb) AS elem
    WHERE (elem ->> 'date_started')::DATE IS NOT NULL
)
MERGE grow.plant_phase AS t
USING phase_list AS s
ON s.id_plant = t.id_plant AND s.id_phase = t.id_phase
WHEN MATCHED AND t.date_started <> s.date_started THEN
    UPDATE SET
        date_started = s.date_started,
        date_updated = (now() AT TIME ZONE 'UTC'),
        updated_by = :id_user
WHEN NOT MATCHED THEN
    INSERT (id_plant, id_phase, date_started, created_by, updated_by)
    VALUES (s.id_plant, s.id_phase, s.date_started, :id_user, :id_user)
WHEN NOT MATCHED BY SOURCE AND t.id_plant = :id_plant THEN
    DELETE;

实现方案二：兼容所有PostgreSQL版本的分步DML方案 #

如果你的环境是低于15的PostgreSQL版本，无法使用原生MERGE，完全可以通过「CTE + 独立DML语句 + 关联判断」实现完全等价的逻辑，全程无需额外插件，且执行性能更可控。
注意：所有DML操作必须放在同一个事务块中执行，保证和原MERGE一致的原子性，避免出现中间状态的数据不一致。

分步实现的代码逻辑：

BEGIN;
-- 1. 先处理匹配成功、需要更新的记录
WITH phase_list AS (
    SELECT :id_plant AS id_plant,
           (elem ->> 'id_phase')::INT AS id_phase,
           (elem ->> 'date_started')::DATE AS date_started
    FROM jsonb_array_elements(:phase_list::jsonb) AS elem
    WHERE (elem ->> 'date_started')::DATE IS NOT NULL
),
matched_records AS (
    SELECT t.id, s.date_started
    FROM grow.plant_phase t
    INNER JOIN phase_list s 
        ON s.id_plant = t.id_plant AND s.id_phase = t.id_phase
    WHERE t.date_started <> s.date_started
)
UPDATE grow.plant_phase t
SET 
    date_started = m.date_started,
    date_updated = (now() AT TIME ZONE 'UTC'),
    updated_by = :id_user
FROM matched_records m
WHERE t.id = m.id;

-- 2. 再处理需要插入的不存在的记录
WITH phase_list AS (
    SELECT :id_plant AS id_plant,
           (elem ->> 'id_phase')::INT AS id_phase,
           (elem ->> 'date_started')::DATE AS date_started
    FROM jsonb_array_elements(:phase_list::jsonb) AS elem
    WHERE (elem ->> 'date_started')::DATE IS NOT NULL
)
INSERT INTO grow.plant_phase (id_plant, id_phase, date_started, created_by, updated_by)
SELECT s.id_plant, s.id_phase, s.date_started, :id_user, :id_user
FROM phase_list s
WHERE NOT EXISTS (
    SELECT 1 FROM grow.plant_phase t
    WHERE t.id_plant = s.id_plant AND t.id_phase = s.id_phase
);

-- 3. 最后处理需要删除的、不在源列表的冗余记录
WITH phase_list AS (
    SELECT DISTINCT (elem ->> 'id_phase')::INT AS id_phase
    FROM jsonb_array_elements(:phase_list::jsonb) AS elem
    WHERE (elem ->> 'date_started')::DATE IS NOT NULL
)
DELETE FROM grow.plant_phase t
WHERE t.id_plant = :id_plant
  AND t.id_phase NOT IN (SELECT id_phase FROM phase_list);
COMMIT;

关键注意事项 #

• 不要直接套用ON CONFLICT DO UPDATE写简化逻辑，原SQL的WHEN MATCHED分支是有明确的“日期不一致才更新”的判断条件，无意义的全量更新会触发不必要的表写入和索引维护
• 删除操作的条件必须严格限定id_plant = :id_plant，不要去掉这个过滤条件，避免误删其他工厂下的阶段关联数据
• 时间函数替换时必须加时区转换，直接用now()会写入带时区的本地时间，和原getutcdate()返回的纯UTC时间存在时区偏差

内容的提问来源于stack exchange，提问作者md-shah