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

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.

"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

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

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.