PostgreSQL+pg-promise调用存储过程更新数据时遇id歧义错误

错误根源 #

这个错误是因为你的update_user存储过程里，id同时作为输入参数名和目标表的列名存在，PostgreSQL无法判断你SQL语句中引用的id到底是哪个，导致歧义。

修复步骤 #

1. 修改存储过程，消除标识符歧义 #

有两种常用方式解决：

方式一：给参数添加前缀（推荐，可读性更高）

给存储过程的参数名加上统一前缀（比如p_），在SQL中明确区分参数和表列：

CREATE OR REPLACE FUNCTION update_user(p_id int, p_name text, p_email text)
RETURNS void AS $$
BEGIN
  UPDATE users 
  SET name = p_name, 
      email = p_email 
  WHERE users.id = p_id; -- 明确指定表的id列和参数p_id
END;
$$ LANGUAGE plpgsql;

方式二：使用位置参数

如果不想修改参数名，可以用PostgreSQL的位置参数$1、$2指代输入参数，避免和表列名冲突：

CREATE OR REPLACE FUNCTION update_user(id int, name text, email text)
RETURNS void AS $$
BEGIN
  UPDATE users u
  SET name = $2, 
      email = $3 
  WHERE u.id = $1; -- $1对应第一个参数id，u.id指代表的列
END;
$$ LANGUAGE plpgsql;

2. 验证Controller层调用的参数匹配 #

检查controller.js中调用存储过程的代码，确保传递的参数顺序和存储过程定义的一致：

// controller.js中的更新方法示例
async function updateUser(req, res) {
  const { id } = req.params;
  const { name, email } = req.body;
  try {
    // 参数顺序必须和存储过程的参数顺序严格对应
    await db.none('CALL update_user($1, $2, $3)', [id, name, email]);
    res.status(200).json({ status: 'success', msg: '用户已更新' });
  } catch (err) {
    res.status(500).json({ status: 'failed', msg: err.message });
  }
}

3. 直接验证存储过程 #

在PostgreSQL客户端（比如psql）直接调用存储过程，确认修复后的逻辑正常：

CALL update_user(1, '张三', 'zhangsan@example.com');

如果执行成功，说明存储过程的问题已经解决，再测试接口即可。

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