The following bug has been logged online:

Bug reference: 2107
Logged by: Tony S
Email address: tony (AT) vectorsalad (DOT) 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
--------------------+--------------------------+---------------------------
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=".

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Tony S" <tony (AT) vectorsalad (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly