如何在SQL中集成LOOP实现elan.elig查询结果分摊更新PMPM表

• 你创建的"UpdatePMPM"函数定义时没有声明入参，调用时传入了3个参数，参数数量不匹配，PostgreSQL找不到对应签名的函数
• PostgreSQL中用双引号包裹的标识符区分大小写，如果你创建函数时写的是"UpdatePMPM"，调用时也必须严格带双引号保持大小写一致，否则会被默认转成小写找不到函数
• 原有函数逻辑存在死循环问题，没有设置循环终止条件，也没有读取elan.elig表数据的逻辑，无法直接满足需求

方案1：单SQL语句实现（更推荐，性能更高） #

不需要自定义函数，直接用PostgreSQL内置的generate_series实现批量更新，避免逐行循环的性能损耗：

WITH elig_metrics AS (
    -- 先计算elig表每一行的会员月数和生效日期
    SELECT
        (EXTRACT(YEAR FROM AGE(CASE WHEN terminationdate IS NULL THEN CURRENT_DATE ELSE terminationdate END, effectivedate))) * 12 +
        (EXTRACT(MONTH FROM AGE(CASE WHEN terminationdate IS NULL THEN CURRENT_DATE ELSE terminationdate END, effectivedate)) + 1) AS mbrmonths,
        effectivedate
    FROM elan.elig
)
-- 批量更新pmpm表对应年月的会员月计数
UPDATE elan.pmpm p
SET mbrmonths = mbrmonths + 1
FROM elig_metrics e
WHERE p.yyyymm = TO_CHAR(e.effectivedate + (generate_series(1, e.mbrmonths::INTEGER) - 1) * INTERVAL '1 month', 'YYYYMM');

方案2：修正自定义函数实现 #

如果需要用函数封装逻辑，先修改函数定义，添加对应入参，修复循环逻辑：

CREATE OR REPLACE FUNCTION "UpdatePMPM"(p_nbr_mem_months INTEGER, p_effectivedate DATE) 
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_ym CHAR(6);
    v_current_date DATE := p_effectivedate;
BEGIN
    -- 按会员月数循环更新对应年月桶
    FOR r IN 1..p_nbr_mem_months LOOP
        v_ym := TO_CHAR(v_current_date, 'YYYYMM');
        UPDATE elan.pmpm SET mbrmonths = mbrmonths + 1 WHERE yyyymm = v_ym;
        v_current_date := v_current_date + INTERVAL '1 month';
    END LOOP;
    RETURN TRUE;
END
$$;

再用DO块遍历elan.elig表所有行，调用函数处理：

DO $$
DECLARE
    v_elig RECORD;
BEGIN
    -- 遍历elig表的每一行计算结果
    FOR v_elig IN (
        SELECT
            (EXTRACT(YEAR FROM AGE(CASE WHEN terminationdate IS NULL THEN CURRENT_DATE ELSE terminationdate END, effectivedate))) * 12 +
            (EXTRACT(MONTH FROM AGE(CASE WHEN terminationdate IS NULL THEN CURRENT_DATE ELSE terminationdate END, effectivedate)) + 1) AS mbrmonths,
            effectivedate
        FROM elan.elig
    ) LOOP
        -- 调用函数处理当前行
        PERFORM "UpdatePMPM"(v_elig.mbrmonths::INTEGER, v_elig.effectivedate);
    END LOOP;
END
$$;

• 执行更新操作前建议先备份elan.pmpm表数据，避免误操作
• 如果elan.elig数据量较大，优先选择方案1的集合更新，执行效率远高于逐行循环调用函数
• 单独测试函数的语法为：SELECT "UpdatePMPM"(5, '2019-04-01'::DATE);，参数数量和类型要和函数定义完全匹配

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