如何编写PostgreSQL函数整合两个更新语句并返回更新后的表

以下是整合两个更新逻辑的PL/pgSQL函数，可直接执行并返回更新后的product_Details表记录：

CREATE OR REPLACE FUNCTION update_product_promotions()
RETURNS SETOF product_Details AS $$
BEGIN
    -- 执行添加促销价操作：匹配pricing表start_date为当前日期的记录
    UPDATE product_Details pd
    SET price_and_price_Type = pd.price_and_price_Type || jsonb_build_object('promo_price', p.promo_price)
    FROM pricing p
    WHERE pd.id = p.product_id
      AND p.start_date = CURRENT_DATE; -- 需精确到时间戳则替换为 CURRENT_TIMESTAMP

    -- 执行移除促销价操作：匹配pricing表end_date为当前日期的记录
    UPDATE product_Details pd
    SET price_and_price_Type = pd.price_and_price_Type - 'promo_price'
    FROM pricing p
    WHERE pd.id = p.product_id
      AND p.end_date = CURRENT_DATE; -- 需精确到时间戳则替换为 CURRENT_TIMESTAMP

    -- 返回所有本次被更新的商品记录
    RETURN QUERY
    SELECT * FROM product_Details
    WHERE id IN (
        SELECT product_id FROM pricing
        WHERE start_date = CURRENT_DATE OR end_date = CURRENT_DATE
    );
END;
$$ LANGUAGE plpgsql VOLATILE;

调用方式 #

执行以下语句即可触发更新并获取结果：

SELECT * FROM update_product_promotions();

常见报错排查 #

1. JSONB操作语法错误：确保你的PostgreSQL版本在9.5及以上（||合并JSONB、-删除键的语法从该版本开始支持）。若版本过低，可改用jsonb_set添加字段，jsonb_delete移除字段：

   • 添加促销价：jsonb_set(pd.price_and_price_Type, '{promo_price}', to_jsonb(p.promo_price))
   • 移除促销价：jsonb_delete(pd.price_and_price_Type, '{promo_price}')

2. 事务一致性问题：函数内的两个更新默认在同一个事务中执行，若其中一个失败会自动回滚所有操作，避免数据不一致。

3. 返回结果不符合预期：若需仅返回实际被修改的记录（而非所有符合日期条件的记录，可能存在重复匹配），可改用RETURNING子句收集每次更新的结果并合并：

CREATE OR REPLACE FUNCTION update_product_promotions()
RETURNS SETOF product_Details AS $$
DECLARE
    temp_recs product_Details;
BEGIN
    -- 添加促销价并返回更新记录
    FOR temp_recs IN
        UPDATE product_Details pd
        SET price_and_price_Type = pd.price_and_price_Type || jsonb_build_object('promo_price', p.promo_price)
        FROM pricing p
        WHERE pd.id = p.product_id AND p.start_date = CURRENT_DATE
        RETURNING *
    LOOP
        RETURN NEXT temp_recs;
    END LOOP;

    -- 移除促销价并返回更新记录
    FOR temp_recs IN
        UPDATE product_Details pd
        SET price_and_price_Type = pd.price_and_price_Type - 'promo_price'
        FROM pricing p
        WHERE pd.id = p.product_id AND p.end_date = CURRENT_DATE
        RETURNING *
    LOOP
        RETURN NEXT temp_recs;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

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