0

I'm trying to call a procedure, but casting the argument, but even performing the casting, the received argument is still the old type.

My procedure:

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$

My call:

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

Error:

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Lucas Emanuel
  • 470
  • 2
  • 10

1 Answers1

4

Casting is not the problem. Your call would even work without any casting at all, with numeric constants or untyped string constants.

CALL transfer_money(1, 2, 100);
CALL transfer_money('1', '2', '100');

Function type resolution would take care of it (unless you have declared overloaded functions that make the call ambiguous). See:

The problem is, that you never created that procedure to begin with. How I know that? Because what you display is syntactical nonsense that would only raise an exception. It's declared as LANGUAGE sql but uses PL/pgSQL elements. (SQL functions have no block structure with BEGIN and END, they consist of SQL commands only.)
Also, a colon where a semicolon is required.

Consider this fixed and simplified version:

CREATE OR REPLACE PROCEDURE transfer_money(_user_origin_id int
                                         , _user_destination_id int
                                         , _amount dec)
  LANGUAGE sql AS
$proc$
UPDATE wallets w
SET    balance = w.balance - _amount
FROM   users u
WHERE  u.id = _user_origin_id
AND    w.id = u.wallet_id;

UPDATE wallets w
SET    balance = w.balance + _amount
FROM   users u
WHERE  u.id = _user_destination_id
AND    w.id = u.wallet_id;

COMMIT;
$proc$;

BTW, while executing the CALL like you display (without nesting in an explicit transaction with more commands), autocommit would take care of the COMMIT anyway, and there would be no need for an explicit COMMIT;.

But maybe more is going on than you show?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228