如何在PostgreSQL中创建并调用函数以消除重复代码

你当前的脚本重复执行了相同的UPDATE逻辑，仅传入的数组数据不同，下面提供三种精简方案，从封装逻辑到更高效的批量操作：

方案1：封装重复逻辑为自定义函数 #

把循环更新的代码封装成可复用的函数，后续只需传入不同数组即可：

-- 创建自定义函数
CREATE OR REPLACE FUNCTION update_profile_order(p_data text[])
RETURNS void AS
$func$
DECLARE
    m text[];
BEGIN
    FOREACH m SLICE 1 IN ARRAY p_data
    LOOP
        UPDATE profile 
        SET order_num = CAST(m[2] AS bigint) 
        WHERE persistent_id = m[1];
    END LOOP;
END
$func$ LANGUAGE plpgsql;

-- 简化后的DO块
DO
$do$
DECLARE
    acc text[] := '{{acc_PROF,1},{acc_PER,2},{cad_PROF,3}}';
    lev text[] := '{{lev_ADMIN,1},{lev_PROF,2}}';
    att text[] := '{{att_ADMIN,1},{att_PROF,2}}';
BEGIN
    PERFORM update_profile_order(acc);
    PERFORM update_profile_order(lev);
    PERFORM update_profile_order(att);
END
$do$ LANGUAGE plpgsql;

方案2：合并数组，单次循环处理 #

直接把三个数组合并成一个大数组，只写一次循环逻辑：

DO
$do$
DECLARE
    m text[];
    all_data text[] := '{{acc_PROF,1},{acc_PER,2},{cad_PROF,3},{lev_ADMIN,1},{lev_PROF,2},{att_ADMIN,1},{att_PROF,2}}';
BEGIN
    FOREACH m SLICE 1 IN ARRAY all_data
    LOOP
        UPDATE profile 
        SET order_num = CAST(m[2] AS bigint) 
        WHERE persistent_id = m[1];
    END LOOP;
END
$do$ LANGUAGE plpgsql;

方案3：批量更新（推荐，性能更优） #

避免PL/pgSQL循环，用PostgreSQL集合操作一次性完成所有更新，效率更高：

DO
$do$
BEGIN
    UPDATE profile p
    SET order_num = d.order_num
    FROM (
        SELECT 
            unnest('{acc_PROF,acc_PER,cad_PROF,lev_ADMIN,lev_PROF,att_ADMIN,att_PROF}'::text[]) AS persistent_id,
            unnest('{1,2,3,1,2,1,2}'::bigint[]) AS order_num
    ) d
    WHERE p.persistent_id = d.persistent_id;
END
$do$ LANGUAGE plpgsql;

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