Re: BUG #2108: Function with OUT parameters not recognized, using plpgsql - Mailing list pgsql-bugs

From Alvaro Herrera
Subject Re: BUG #2108: Function with OUT parameters not recognized, using plpgsql
Date
Msg-id 20051212140835.GH19555@surnet.cl
Whole thread Raw
In response to BUG #2108: Function with OUT parameters not recognized, using plpgsql  ("Tony" <tony@vectorsalad.com>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #2108: Function with OUT parameters not recognized, using plpgsql
Next
From: "Harry Rossignol"
Date:
Subject: PQexecParams performance