Postgres循环内多次UPDATE后能否返回所有受影响行？

一、优先方案：原生SQL（无循环） #

PostgreSQL支持CTE（公共表表达式）和RETURNING子句，可以无需循环直接完成需求，同时返回所有被更新的行，性能更优且符合原生SQL要求。

实现代码 #

WITH new_orders AS (
    SELECT * FROM new_orders_by_date
),
-- 插入不存在的订单日期记录（初始计数为0）
inserted_rows AS (
    INSERT INTO ordercountsbyorderdate (item, orderdate, ordercount)
    SELECT n.item, n.orderdate, 0
    FROM new_orders n
    WHERE NOT EXISTS (
        SELECT 1 FROM ordercountsbyorderdate o
        WHERE o.item = n.item AND o.orderdate = n.orderdate
    )
    RETURNING * -- 若不需要插入的行可忽略此子句
),
-- 更新符合条件的所有记录，并返回更新后的行
updated_rows AS (
    UPDATE ordercountsbyorderdate o
    SET ordercount = o.ordercount + n.ordercount
    FROM new_orders n
    WHERE o.item = n.item AND o.orderdate >= n.orderdate
    RETURNING o.*
)
-- 最终返回所有被更新的行
SELECT * FROM updated_rows;

逻辑说明 #

1. new_orders CTE获取所有待处理的辅助数据；
2. inserted_rows CTE负责插入缺失的item+orderdate组合记录，初始计数设为0；
3. updated_rows CTE完成批量更新：对每条辅助数据，将所有>=对应orderdate的同item记录的ordercount累加；
4. 最后通过SELECT * FROM updated_rows返回所有被更新的行。

二、函数方案（兼容循环逻辑） #

如果因业务逻辑限制必须使用循环，可创建PL/pgSQL函数返回更新后的行集合。

实现代码 #

-- 创建函数，返回ordercountsbyorderdate表的行类型集合
CREATE OR REPLACE FUNCTION update_order_counts()
RETURNS SETOF ordercountsbyorderdate AS $$
DECLARE
    new_order RECORD;
    updated_row ordercountsbyorderdate;
BEGIN
    -- 遍历辅助数据
    FOR new_order IN (SELECT * FROM new_orders_by_date) LOOP
        -- 检查是否存在对应记录，不存在则插入初始计数0
        IF NOT EXISTS (
            SELECT 1 FROM ordercountsbyorderdate
            WHERE item = new_order.item AND orderdate = new_order.orderdate
        ) THEN
            INSERT INTO ordercountsbyorderdate (item, orderdate, ordercount)
            VALUES (new_order.item, new_order.orderdate, 0);
        END IF;

        -- 执行更新，并收集每一次更新的行
        FOR updated_row IN
            UPDATE ordercountsbyorderdate
            SET ordercount = ordercount + new_order.ordercount
            WHERE item = new_order.item AND orderdate >= new_order.orderdate
            RETURNING *
        LOOP
            RETURN NEXT updated_row; -- 将当前更新行加入结果集
        END LOOP;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

调用方式 #

SELECT * FROM update_order_counts();

原DO块无法返回结果的原因 #

PostgreSQL的DO块是匿名PL/pgSQL过程，没有返回值定义，因此无法通过RETURNING或RETURN语句返回结果。若需要返回数据，必须使用函数或原生SQL的集合操作。

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