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=".
