Thread: BUG #2107: Function INOUT parameter not returned to caller, causes plpgsql malfunctions

The following bug has been logged online:

Bug reference:      2107
Logged by:          Tony S
Email address:      tony@vectorsalad.com
PostgreSQL version: 8.1.0
Operating system:   Debian 1:3.3.5-8ubuntu2, 2.6.10-5-k7, i686
Description:        Function INOUT parameter not returned to caller, causes
plpgsql malfunctions
Details:

Function defined with INOUT parameter.  Value of parameter is not returned
to calling function.  Furthermore (which may be the same problem?), the
returned variable causes plpgsql to null out calculations when it is used.

(Note: I used INOUT instead of just OUT because of issues with that, which I
will file separately.)

Example:

First function has the INOUT parameter, the remaining three are used to test
it.

<code>

CREATE OR REPLACE FUNCTION f_multiparam (
  i1 integer,
  i2 varchar,
  INOUT o1 varchar
) AS
$$
BEGIN
  o1 := 'i2 was ' || i2;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_showoutparameter () RETURNS varchar AS
$$
DECLARE
  outparameter varchar;
  returnvalue varchar;
BEGIN
  returnvalue = f_multiparam(1, 'hello', outparameter);
  RETURN 'outparameter=' || outparameter;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_showreturnvalue () RETURNS varchar AS
$$
DECLARE
  outparameter varchar;
  returnvalue varchar;
BEGIN
  returnvalue = f_multiparam(1, 'hello', outparameter);
  RETURN 'returnvalue=' || returnvalue;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_showperformoutparameter () RETURNS varchar AS
$$
DECLARE
  outparameter varchar;
BEGIN
  PERFORM f_multiparam(1, 'hello', outparameter);
  RETURN 'outparameter=' || outparameter;
END;
$$
LANGUAGE plpgsql;

select f_showoutparameter(), f_showreturnvalue(),
f_showperformoutparameter();

</code>

Output is:

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
 f_showoutparameter |    f_showreturnvalue     | f_showperformoutparameter
--------------------+--------------------------+---------------------------
                    | returnvalue=i2 was hello |

Expected Results:

f_showoutparameter()
Didn't really expect this to compile/work as the f_multiparam function has
no return value.  Anyway, it does run, and the outparameter isn't returned.
Furthermore, it has corrupted the calculation in the final RETURN statement
of f_showoutputparameter().  At the very least, I would have expected it to
return "outparameter=".

f_showreturnvalue()
Didn't really expect this to compile/work as the f_multiparam function has
no return value.  Anyway, it does run, and the return value is what I was
expecting in the out parameter.

f_showperformoutparameter()
This is what I expected to work, but there appears to be no out parameter.
Furthermore, it has corrupted the calculation in the final RETURN statement
of f_showperformoutparameter().  At the very least, I would have expected it
to return "outparameter=".

"Tony S" <tony@vectorsalad.com> writes:
> Function defined with INOUT parameter.  Value of parameter is not returned
> to calling function.

You are confused about the meaning and use of INOUT.  It's not some kind
of pass-by-reference parameter, it's just a shorthand for separate IN
and OUT parameters.  In your example, the PERFORM discards the function
result; the original value of 'outparameter' is not and cannot be
modified by the called function.

            regards, tom lane

Tom Lane wrote:
> "Tony S" <tony@vectorsalad.com> writes:
>
>>Function defined with INOUT parameter.  Value of parameter is not returned
>>to calling function.
>
>
> You are confused about the meaning and use of INOUT.  It's not some kind
> of pass-by-reference parameter, it's just a shorthand for separate IN
> and OUT parameters.  In your example, the PERFORM discards the function
> result; the original value of 'outparameter' is not and cannot be
> modified by the called function.
>
>             regards, tom lane


This is very much my mistake.  I had indeed taken them to be a sort of
pass-by-reference parameter, and not part of the result definition,
which they actually are.  Running PERFORM is pointless, then, too.