PostgreSQL Bugs - BUG #2107: Function INOUT parameter not returned to caller, causes plpgsql malfunctions

Author

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

Tony S

2005-12-12, 7:24 am

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_showperformoutpara
meter () 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_showperformoutpara
meter();

</code>

Output is:

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
f_showoutparameter | f_showreturnvalue | f_showperformoutpara
meter
--------------------+--------------------------+---------------------------
| 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_showoutputparamete
r(). 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_showperformoutpara
meter()
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_showperformoutpara
meter(). 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

Tom Lane

2005-12-12, 11:24 am

"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

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

Sponsored Links