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
3
Answers
You need to typecast the parameters. This will work, allthough you’ll get other errors when the procedure does it’s things.
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:
Be careful with that conversion to timestamp as the result will depend on the timezone settings of the SQL client.
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 aninteger
, which cannot be cast tosmallint
implicitly. You’d have to use an explicit type cast likeCAST (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 numbersnumeric
and for date/time it istimestamp with time zone
. Then the type resolution rules will usually work the way you want.