存储过程调用时类型转换是否为问题？参数匹配报错求助

调用自定义存储过程时，已对传入参数做类型转换，但仍触发参数类型不匹配报错，怀疑类型转换存在问题。以下是相关代码与报错信息：

存储过程定义 #

CREATE PROCEDURE transfer_money(
 user_origin_id int,
 user_destination_id int,
 amount dec
)
LANGUAGE sql
AS $delimiter$
DECLARE 
  wallet_origin_id integer,
  wallet_destination_id integer;
BEGIN
  SELECT wallet_id INTO wallet_origin_id
  FROM users
  WHERE id = user_origin_id;

  SELECT wallet_id INTO wallet_destination_id
  FROM users
  WHERE id = user_destination_id;

  UPDATE wallets
  SET balance = balance - amount
  WHERE id = wallet_origin_id;

  UPDATE wallets
  SET balance = balance + amount
  WHERE id = wallet_destination_id;

  commit;
END;
$delimiter$

调用语句 #

CALL transfer_money(1, 2, cast(100.00 as DECIMAL));

报错信息 #

ERROR:  procedure transfer_money(integer, integer, numeric) does not exist
LINE 1: CALL transfer_money(1, 2, cast(100.00 as DECIMAL));
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6

问题原因与解决方法 #

1. 存储过程语言类型错误：你定义存储过程时用了LANGUAGE sql，但代码里包含了PL/pgSQL的语法（DECLARE、BEGIN、COMMIT等），这会导致存储过程无法正确创建或解析，必须改为LANGUAGE plpgsql。

2. 变量声明语法错误：DECLARE块里的变量之间应该用分号(;)分隔，而不是逗号(,)，否则会触发语法错误。

3. 参数类型不匹配：PostgreSQL中dec是decimal的缩写，但未指定精度和刻度的dec与带默认精度的DECIMAL会被视为不同类型。你可以：

   • 调用时显式转换为无精度的dec；
   • 或者修改存储过程参数，明确指定与调用时一致的精度（比如amount decimal(10,2)），确保类型完全匹配。

修正后的存储过程 #

CREATE PROCEDURE transfer_money(
 user_origin_id int,
 user_destination_id int,
 amount dec
)
LANGUAGE plpgsql
AS $delimiter$
DECLARE 
  wallet_origin_id integer;
  wallet_destination_id integer;
BEGIN
  SELECT wallet_id INTO wallet_origin_id
  FROM users
  WHERE id = user_origin_id;

  SELECT wallet_id INTO wallet_destination_id
  FROM users
  WHERE id = user_destination_id;

  UPDATE wallets
  SET balance = balance - amount
  WHERE id = wallet_origin_id;

  UPDATE wallets
  SET balance = balance + amount
  WHERE id = wallet_destination_id;

  commit;
END;
$delimiter$

正确调用方式（二选一） #

• 方式1：转换为dec类型

CALL transfer_money(1, 2, cast(100.00 as dec));

• 方式2：如果修改存储过程参数为decimal(10,2)，调用可直接写：

CALL transfer_money(1, 2, 100.00);

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