AWS Redshift如何将整列作为参数传入存储过程并逐行计算回填

首先提前执行ALTER语句新增目标字段，可根据g_key实际返回值调整字段类型：

ALTER TABLE temp ADD COLUMN g_key VARCHAR(256);

方案1：游标遍历包装存储过程（无需修改原有逻辑，兼容性最高） #

适合不能改动原有my_schema.sp_calculation存储逻辑、数据量较小的场景，通过新增一个包装存储过程遍历temp表逐行调用计算：

CREATE OR REPLACE PROCEDURE my_schema.batch_calculate_g_key()
LANGUAGE plpgsql
AS $$
DECLARE
    -- 定义游标遍历temp表的经纬度和行唯一标识
    cur CURSOR FOR SELECT latitude, longitude, ctid FROM temp;
    v_lat FLOAT8;
    v_lng FLOAT8;
    v_ctid TID;
    v_g_key VARCHAR(256); -- 和g_key字段类型保持一致
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_lat, v_lng, v_ctid;
        EXIT WHEN NOT FOUND;
        
        -- 调用原有存储过程获取g_key，参数顺序、数量和原存储过程要求匹配
        -- 原存储过程的返回值通过OUT参数v_g_key接收
        CALL my_schema.sp_calculation(v_lat, v_lng, v_g_key);
        
        -- 回填计算结果到当前行，若temp表有主键，用主键条件替代ctid性能更好
        UPDATE temp SET g_key = v_g_key WHERE ctid = v_ctid;
    END LOOP;
    CLOSE cur;
END;
$$;

-- 执行即可完成全表g_key回填
CALL my_schema.batch_calculate_g_key();

方案2：改写为标量UDF（性能最优，支持整列计算） #

如果可以迁移原有存储过程的计算逻辑，优先选择该方案。UDF支持直接传入列值批量计算，不需要逐行遍历，性能远高于游标方案：

-- 创建和原有存储过程逻辑一致的标量UDF
CREATE OR REPLACE FUNCTION my_schema.udf_calculate_g_key(p_latitude FLOAT8, p_longitude FLOAT8)
RETURNS VARCHAR(256)
STABLE
LANGUAGE plpgsql
AS $$
BEGIN
    -- 此处替换为原sp_calculation中的核心计算逻辑
    -- 示例：RETURN 计算得到的g_key结果
END;
$$;

-- 单条UPDATE即可完成全表整列计算回填
UPDATE temp SET g_key = my_schema.udf_calculate_g_key(latitude, longitude);

注意：如果原有存储过程包含事务操作、DDL操作等UDF不支持的逻辑，无法使用该方案。

方案3：分批次批量处理（适合超大数据量表） #

如果temp表数据量超过百万级，游标遍历容易触发事务超时，可先给temp表新增批次号字段，将数据均匀拆分为多个小批次，逐批次执行方案1的遍历逻辑，降低单次事务的运行时长和锁范围。

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