如何声明变量并基于前一行更新数据，无需EXECUTE与字符串拼接？

问题背景 #

我需要循环遍历table1表，通过PL/pgSQL函数声明的_previous_value变量更新每行的value列——该变量用于计算下一行的value值。当前实现使用EXECUTE拼接字符串执行更新，希望找到无需字符串拼接和EXECUTE的正确实现方式。已知可以用LAG()函数获取前一行值，但此场景必须通过函数实现。

表结构 #

create table table1(id,"date",quantity,"value")as values
 (1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'2024-10-05',1,1)
,(6,'2024-10-06',1,1)
,(7,'2024-10-07',1,1);

原实现函数 #

CREATE OR REPLACE FUNCTION functiontest() 
RETURNS VOID LANGUAGE plpgsql AS $f$
DECLARE _r RECORD;
DECLARE _previous_value DOUBLE PRECISION := 0;
BEGIN

FOR _r IN
    SELECT table1.date, table1.id  
    FROM table1
    ORDER BY table1.date, table1.id
LOOP
    WITH c AS
    (   SELECT 
        table1.id,
        table1.quantity + _previous_value AS total
        FROM table1 
        WHERE table1.id = _r.id 
        ORDER BY table1.date, table1.id
        LIMIT 1
    )
    SELECT
    COALESCE(c.total, 0)
    INTO
    _previous_value
    FROM c;

    EXECUTE 
    'UPDATE table1 SET value = ' || _previous_value || 
    ' WHERE table1.id = ' || _r.id || ';';
END LOOP;
END $f$;

改进后的实现 #

原实现存在两个主要问题：一是字符串拼接SQL存在SQL注入风险，二是循环内重复查询表造成冗余。以下是无需EXECUTE的优化版本：

CREATE OR REPLACE FUNCTION functiontest() 
RETURNS VOID LANGUAGE plpgsql AS $f$
DECLARE
    _r RECORD;
    _previous_value DOUBLE PRECISION := 0;
BEGIN
    -- 直接在循环查询中获取需要的quantity字段，避免二次查询
    FOR _r IN
        SELECT id, quantity
        FROM table1
        ORDER BY "date", id
    LOOP
        -- 直接计算当前行的累加值
        _previous_value := _r.quantity + _previous_value;
        
        -- 直接使用变量执行更新，无需字符串拼接和EXECUTE
        UPDATE table1 
        SET "value" = _previous_value
        WHERE id = _r.id;
    END LOOP;
END $f$;

优化说明 #

• 减少冗余查询：循环的SELECT语句直接获取id和quantity，无需在循环内再次查询表获取quantity值
• 消除SQL注入风险：直接在UPDATE语句中引用PL/pgSQL变量，完全避免字符串拼接SQL的操作
• 提升执行效率：去掉不必要的CTE和EXECUTE调用，简化逻辑的同时降低执行开销

如果需要处理并发场景，可在循环查询中添加FOR UPDATE子句锁定行，防止更新时出现数据不一致：

FOR _r IN
    SELECT id, quantity
    FROM table1
    ORDER BY "date", id
    FOR UPDATE
LOOP
    -- 后续逻辑不变
END LOOP;

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