Error calling procedure in posgres "No procedure matches the given name and argument types. You-CodePudding

CREATE or replace PROCEDURE mytransactions (n_transactions_id VARCHAR,
n_transaction_amount SMALLINT, 
n_transaction_date TIMESTAMP,
n_Delivery_date Date,
n_customer_id VARCHAR,
n_product_id VARCHAR,
n_store_id VARCHAR)
LANGUAGE plpgsql AS
  $BODY$
      BEGIN
        INSERT INTO transactions
(transactions_id, 
  transaction_amount,
  transaction_date,
Delivery_date,
customer_id,
product_id,
store_id)
        VALUES
  (n_transactions_id, n_transaction_amount, 
n_transaction_date,
n_Delivery_date,
n_customer_id,
n_product_id,
n_store_id);
      END;
  $BODY$

Here is my stored procedure, it creates successfully, however once I call

CALL mytransactions
('555', 3, current_timestamp , to_date('2022-10-25','YYYY-MM-DD'), 
  '003', '300', '002RW');

it I get an error.

ERROR:  procedure mytransactions(unknown, integer, timestamp with time zone, date, unknown, unknown, unknown) does not exist
LINE 1: CALL mytransactions
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

Here you can find full tables https://dbfiddle.uk/9_NIQDw6

CodePudding user response：

You need to typecast the parameters. This will work, allthough you'll get other errors when the procedure does it's things.

CALL mytransactions
('555'::varchar, 3::smallint, current_timestamp::timestamp , to_date('2022-10-25','YYYY-MM-DD'), 
  '003'::varchar, '300'::varchar, '002RW'::varchar);

CodePudding user response：

The issue is with your second and third arguments. As you can see from the error, Postgres is assuming the second argument (3) is an integer, not smallint and the third argument is created using current_timestamp, which returns a timestamp with time zone, not a timestamp.

You can fix the smallint issue by simply casting to a smallint. For the timestamp, you'll need to figure out what value you actually want in there. I would recommend using timestamp with time zone everywhere, if possible.

Here's an example of calling your procedure that will work, but the timestamp may not be what you actually want:

CALL mytransactions
('555', 3::smallint, current_timestamp::timestamp , to_date('2022-10-25','YYYY-MM-DD'), 
  '003', '300', '002RW');

Be careful with that conversion to timestamp as the result will depend on the timezone settings of the SQL client.

CodePudding user response：

Your argument data types don't match the parameter data types, and there are no implicit type casts that can be applied. For example, 3 is an integer, which cannot be cast to smallint implicitly. You'd have to use an explicit type cast like CAST (3 AS smallint).

To avoid that problem, it is a good practice to use preferred data types for function parameters. Each class of data type has one of them. For string types, it is text, for numbers numeric and for date/time it is timestamp with time zone. Then the type resolution rules will usually work the way you want.