如何创建包含两次查询并关联更新操作的PostgreSQL存储过程？

你的示例代码存在几个关键问题：未将查询结果赋值到变量、未校验用户合法性、缺乏余额校验，以及事务处理不规范。以下是修正后的完整实现：

问题分析 #

1. 直接执行SELECT但未将结果存入变量，后续引用id_user_origin会触发未定义变量错误
2. 未校验转入/转出用户是否存在，可能导致无效的空更新
3. 未校验转出方余额是否充足，可能产生负余额
4. 事务处理不符合PostgreSQL存储过程的最佳实践

修正后的存储过程 #

create or replace procedure transfer(
    origin int,
    destination int, 
    amount dec(18,2) -- 明确金额精度，避免精度丢失
)
language plpgsql    
as $$
declare
    -- 定义变量存储查询到的用户ID与转出方余额
    id_user_origin int;
    id_user_destination int;
    origin_balance dec(18,2);
begin
    -- 1. 查询转出方用户ID，同时校验用户存在
    select id into strict id_user_origin
    from users
    where id = origin;

    -- 2. 查询转入方用户ID，同时校验用户存在
    select id into strict id_user_destination
    from users
    where id = destination;

    -- 3. 校验转出方余额是否充足
    select balance into strict origin_balance
    from wallets
    where id = id_user_origin;

    if origin_balance < amount then
        raise exception '转出方余额不足，当前余额: %，需转出金额: %', origin_balance, amount;
    end if;

    -- 4. 执行转账操作
    -- 扣除转出方金额
    update wallets 
    set balance = balance - amount 
    where id = id_user_origin;

    -- 添加转入方金额
    update wallets
    set balance = balance + amount 
    where id = id_user_destination;

    -- PostgreSQL存储过程默认继承外部事务，无需手动commit；若需独立事务可自行声明
exception
    when no_data_found then
        raise exception '用户ID % 或 % 不存在', origin, destination;
    when others then
        rollback;
        raise;
end;$$

关键说明 #

• 使用SELECT INTO STRICT：确保查询返回且仅返回一条结果，若无数据会触发no_data_found异常，避免空变量引发后续错误
• 余额校验：防止转出方余额不足导致负余额，符合金融业务逻辑
• 异常处理：捕获用户不存在、余额不足等异常，进行友好提示并回滚事务
• 类型精度：明确amount和余额的精度为dec(18,2)，适配金融场景的精度要求
• 事务处理：PostgreSQL存储过程默认使用调用方的事务上下文，无需手动commit；若需要独立事务，可在begin后添加begin transaction并在异常中回滚

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