Stored Procedure Error Message - adding explicit type casts?-CodePudding

This is my first post as I am trying to become more familiar with SQL. Im working on a project where I have to create a stored procedure, but I cannot figure out what this error message is asking me to do. I want the procedure to calculate the sum of the total profit for the date being input. order_date is data type DATE - and it accepts the procedure, but when I call the procedure and input the date, it gives the error below. I have been troubleshooting this for HOURS and any input would be great!

CREATE OR REPLACE PROCEDURE daily_profits (IN order_date DATE, OUT total_profit INTEGER)
    LANGUAGE plpgsql
    AS
    $$
        BEGIN
             SELECT SUM(profit) AS total_profit
             FROM orders
             WHERE orders.order_date = daily_profits.order_date;
        END;
    $$; 
    

CALL daily_profits('2016-10-11');

ERROR:  procedure daily_profits() does not exist
LINE 1: CALL daily_profits(); 
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6

CodePudding user response：

You are passing a string into the SP and it us expecting a date - so you need to cast '2016-10-11' to a date

CodePudding user response：

You need to do:

CALL daily_profits('2016-10-11', NULL);

per here Function/procedure arguments:

In a call to a procedure, all the parameters must be specified. For output parameters, NULL may be specified when calling the procedure from plain SQL:

So:

CREATE OR REPLACE PROCEDURE utility.daily_profits(IN order_date date, OUT total_profit integer)
 LANGUAGE plpgsql
AS $procedure$
        BEGIN
            RAISE NOTICE '%', order_date;
        END;
    $procedure$

then:

CALL daily_profits('2016-10-11', null);
NOTICE:  10/11/2016
 total_profit 
--------------
         NULL