PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why OUT parameter can't be empty in Procedure CALL

CREATE OR REPLACE PROCEDURE test1(OUT i int)
LANGUAGE plpgsql AS
$$
  BEGIN
    i:=10;
end;
$$;

CALL test1(1); --Works. Return 10. Passed value is ignored.
CALL test(); --Doesn't work. ERROR: procedure test1() does not exist Hint: No procedure matches the given name and argument types. You might need to add explicit type casts. Position: 6

The passed argument won’t be assigned as initial value if it is OUT. I know both OUT and INOUT parameter of the procedure must correspond to a variable in the CALL statement. But why does it implemented this way?. Is there any specific reason?.

Tags:

2

Answers


  1. 0

    In SQL procedures, the OUT parameters are meant to capture the values generated by the procedure and return them to the calling environment. Unlike IN parameters, which are used to pass values into the procedure, OUT parameters are used to pass values out of the procedure.

    When calling a procedure with OUT parameters, you need to provide variables to capture the output values. You cannot have an empty list of parameters for a procedure call with OUT parameters because there would be no way to capture the output values.

    For example, if you have a procedure with an OUT parameter like this:

    CREATE OR REPLACE PROCEDURE example_procedure(OUT out_param INT)
LANGUAGE plpgsql AS
$$
BEGIN
  out_param := 42;
END;
$$;

    You would call it like this:

    DO $$ 
DECLARE 
  result INT; 
BEGIN 
  CALL example_procedure(result); 
  -- Now, the variable 'result' holds the value returned by the procedure.
END $$;

    Here, result is a variable that captures the value returned by the OUT parameter of the procedure. The OUT parameter cannot be empty in the procedure call because there needs to be a way to capture the output value.

    Login or Signup to reply.
  2. 0

    The reason why you need to pass a parameter is because there is one in the procedure definition. PostgreSQL has overloading of functions and procedures, and the argument allows PostgreSQL to determine if it should invoke a procedure test1 that has no parameters, one that has an integer parameter or one that has a varchar parameter.

    You can find that in the documentation:

    Arguments must be supplied for all procedure parameters that lack defaults, including OUT parameters. However, arguments matching OUT parameters are not evaluated, so it’s customary to just write NULL for them.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search