PostgreSQL存储过程：按条件更新列值及处理重复插入

你的存储过程目前存在几个关键问题：数组关联方式错误导致笛卡尔积、未实现冲突时的更新逻辑、无法处理同一员工多次传入时取最后组织的需求。下面是修正后的完整方案，完全符合你的业务规则：

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE creator(SURVEY_IDENTIFIER uuid, EMPLOYEES VARCHAR[], ORGANIZATION_NAMES VARCHAR[]) AS $FUNCTION$
BEGIN
    -- 预处理输入数据：关联员工与对应组织，保留每个员工最后传入的记录
    WITH processed_data AS (
        SELECT 
            SURVEY_IDENTIFIER AS survey_id,
            e.name AS employee,
            o.name AS organization_name
        FROM UNNEST(EMPLOYEES) WITH ORDINALITY AS e(name, idx)
        JOIN UNNEST(ORGANIZATION_NAMES) WITH ORDINALITY AS o(name, idx)
            ON e.idx = o.idx
        -- 按员工分组，仅保留序号最大的条目（即最后传入的那条）
        QUALIFY ROW_NUMBER() OVER (PARTITION BY e.name ORDER BY e.idx DESC) = 1
    )
    INSERT INTO RELATIONSHIP (SURVEY_ID, EMPLOYEE, ORGANIZATION_NAME)
    SELECT survey_id, employee, organization_name
    FROM processed_data
    ON CONFLICT ON CONSTRAINT RELATIONSHIP_UNIQUE_KEY 
    DO UPDATE 
        -- 仅当原记录的STATUS为false时，更新组织名称
        SET ORGANIZATION_NAME = EXCLUDED.ORGANIZATION_NAME
        WHERE RELATIONSHIP.STATUS = false;
END;
$FUNCTION$ LANGUAGE plpgsql;

关键改进点说明 #

• 正确关联数组元素
  原代码直接UNNEST两个数组会产生笛卡尔积，导致数据匹配错误。这里用WITH ORDINALITY给每个数组元素添加序号，通过序号关联员工和对应的组织，确保一一对应。

• 处理同一员工多次传入的情况
  使用QUALIFY和窗口函数ROW_NUMBER()，按员工分组后取序号最大的记录（也就是最后传入的那条），确保同一员工多次出现时，最终使用最后一个传入的组织名称。

• 实现冲突时的更新规则
  通过ON CONFLICT ... DO UPDATE语法，在唯一键冲突时：

  • 不插入新记录（符合规则2）
  • 仅当原记录的STATUS为false时，才更新ORGANIZATION_NAME为新传入的值（对应Mark的记录更新，Bob的记录因STATUS为true不修改）

• 符合插入规则
  对于唯一键组合不存在的记录（比如Kate的条目），正常插入，STATUS使用默认值false。

测试验证 #

调用你提供的测试语句：

CALL creator( 'd5f9c639-13e6-42c1-9043-30783981724b', ARRAY['Mark', 'Bob', 'Kate'], ARRAY['Google', 'Google', 'HP'] );

执行后，表的最终结果会符合你的预期：

如果测试同一员工多次传入的场景，比如：

CALL creator( 'd5f9c639-13e6-42c1-9043-30783981724b', ARRAY['Mark', 'Mark', 'Kate'], ARRAY['Google', 'Amazon', 'HP'] );

Mark的组织会被更新为Amazon（最后传入的值），而Bob的记录保持不变，Kate的记录正常插入。

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