How to create a stored procedure in Postgres and return value?-CodePudding

I am trying to create a stored procedure in PostgreSQL. I have this script to create a stored procedure:

create or replace procedure Duplicate_config_v1(
                        sourcequoteid in int,
                        sourceconfig in varchar,
                        destinationquote in int,
                        scenarioid in int,
                        flag inout varchar(10))
                        LANGUAGE plpgsql
                        as $$
                        begin
                        select Status::varchar(10) from public.duplicate_scenario_statuses where id = 1;
                        --returning 'Sucess'into flag;
                        end $$;

I created the stored procedure as shown above. In the procedure I am passing few input parameters and returning some value from table. I am trying to call procedure as below

Call duplicate_config_v1(1,'1'::varchar(10),1,1);

Whenever I execute this statement, I get an error:

ERROR: procedure duplicate_config_v1(integer, character varying, integer, integer) does not exist
LINE 14: Call duplicate_config_v1(1,'1'::varchar(10),1,1);
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 592

Can someone help me to identify the issue here? Any help would be appreciated. Thanks

CodePudding user response：

Just use function. In manual 43.6.3. Calling a Procedure the example is something like

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

that is very simple, then you can call the procedure to get the value.

manual 43.6.2. Returning from a Procedure

A procedure does not have a return value. A procedure can therefore end without a RETURN statement. If you wish to use a RETURN statement to exit the code early, write just RETURN with no expression.

If the procedure has output parameters, the final values of the output parameter variables will be returned to the caller.

Meaning, that the value will return to the caller, but you need extract step to get it. That's why when " case if return value" people recommend function.

the following demo use DO COMMAND to get the value from caller.

CREATE temp TABLE duplicate_scenario_statuses (
    id int,
    sourcequoteid int,
    sourceconfig text,
    destinationquote int,
    status text,
    scenarioid int,
    flag text
);

INSERT INTO duplicate_scenario_statuses (id, status)
    VALUES (1, 'hello');

CREATE OR REPLACE PROCEDURE pg_temp.Duplicate_config_v1 (sourcequoteid IN int, sourceconfig IN text, destinationquote IN int, scenarioid IN int, flag OUT text)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT
        status INTO flag
    FROM
        duplicate_scenario_statuses
    WHERE
        id = 1;
END
$$;

DO $$
DECLARE
    _status text;
BEGIN
    CALL pg_temp.Duplicate_config_v1 (1, '1', 1, 1, _status);
    RAISE NOTICE '_status: %', _status;
END;
$$;

CodePudding user response：

Your procedure is defined with 5 parameters, but you only provide 4. Thus the procedure definition is not found and your CALL results in " No procedure matches the given name and argument types".

You need to provide a dummy value for the inout parameter, e.g. NULL:

call duplicate_config_v1(1,'1',1,1,null);

To "return" a value, just assign it:

flag := 'Success';

Online example