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

Author

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

Tony

2005-12-12, 7:24 am

The following bug has been logged online:

Bug reference: 2108
Logged by: Tony
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 with OUT parameters not recognized, using
plpgsql
Details:

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

Example:

<code>

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;

select f_showperformstatus(
);

</code>

Output:

CREATE FUNCTION
CREATE FUNCTION
psql:bug2.sql:24: ERROR: function f_multiparam(integer
, "unknown",
character varying) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
CONTEXT: SQL statement "SELECT f_multiparam(1, 'hello', $1 )"
PL/pgSQL function "f_showperformstatus" line 4 at perform

It appears that the function is not defined properly in the system, with
only 2 parameters instead of 3:

\df f_multiparam
List of functions
Schema | Name | Result data type | Argument data types
--------+--------------+-------------------+----------------------------
apps | f_multiparam | character varying | integer, character varying

Explicitly casting the value 'hello' as suggested does not help. Changing
the function definition from OUT to INOUT parameter is a successful
workaround.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2005-12-12, 11:24 am

"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/...TPUT-PARAMETERS

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera

2005-12-12, 1:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links