BUG #2108: Function with OUT parameters not recognized, using plpgsql

Tony wrote:

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

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

You are misunderstanding how are functions with OUT params supposed to
be called, I think. Try this:

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

CREATE OR REPLACE FUNCTION f_showperformstatus () RETURNS varchar AS
$$
DECLARE
outparameter varchar;
BEGIN
SELECT INTO outparameter f_multiparam(1, 'hello');
RAISE NOTICE 'the out param is %', outparameter;
RETURN 'successfully run';
END;
$$
LANGUAGE plpgsql;

The output I get is what I'd expect:

alvherre=# select f_showperformstatus();
NOTICE: the out param is i2 was hello
f_showperformstatus
---------------------
successfully run
(1 fila)

I think this also applies to your INOUT report, but I haven't checked.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Stallone wrote:

Please keep replies on the list.

What you have done is run a SELECT which evaluates the function
f_multiparam() passing it two parameters, and then takes the result and
puts it INTO a local parameter. This is not the same. An OUT parameter
is actually passed to the function and is part of the function
specification, in this case it is defined as the third parameter. It's
like a placeholder within the definition of the function itself, and it
should show up on the list of parameters for that function. You can, in
fact, have many OUT parameters in one function. This is an advantage of
out parameters over just a plain function with a single RETURN element.

At least this is how it has always worked for me.

Has always worked where? In Postgres? It's strange that you mention
"always" because OUT parameters are new in Postgres 8.1. Behavior in
other database systems is not directly applicable to Postgres.

Keep in mind that in Postgres we don't have host variables, which is
what is needed to make OUT params work the way you are assuming they do.
This could be improved in the future but currently that's the way it is.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

"Tony" <tony@vectorsalad.com> writes:

Defined a function with OUT paramter. Attempts to call it fail as the
function can not be found.

Apparently, you don't understand how OUT parameters work either :-(
Perhaps the examples here will help:
http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

regards, tom lane

Alvaro Herrera wrote:

Stallone wrote:

Please keep replies on the list.

What you have done is run a SELECT which evaluates the function
f_multiparam() passing it two parameters, and then takes the result and
puts it INTO a local parameter. This is not the same. An OUT parameter
is actually passed to the function and is part of the function
specification, in this case it is defined as the third parameter. It's
like a placeholder within the definition of the function itself, and it
should show up on the list of parameters for that function. You can, in
fact, have many OUT parameters in one function. This is an advantage of
out parameters over just a plain function with a single RETURN element.

At least this is how it has always worked for me.

Has always worked where? In Postgres? It's strange that you mention
"always" because OUT parameters are new in Postgres 8.1. Behavior in
other database systems is not directly applicable to Postgres.

Keep in mind that in Postgres we don't have host variables, which is
what is needed to make OUT params work the way you are assuming they do.
This could be improved in the future but currently that's the way it is.

I have seriously mistaken the nature of IN/OUT parameters in Postgres.

This misunderstanding all leaked over from Oracle and is not applicable
at all here. Maybe this might make a good bullet point to add in Sec
36.11 "Porting from Oracle PL/SQL". I will post a note separately there.