Re: How to Declare Functions Containing OUT PArameters?

Hello

PostgreSQL use OUT params very untypically. You can't to directly to
join OUT parameter with some variable. It isn't possible.

please, try

CREATE OR REPLACE FUNCTION foo(a int, b int, OUT c int, OUT d int)
RETURNS record AS $$
BEGIN
c := a + 1;
d := b + 1;
RETURN;
END;
$$ LANGUAGE plpgsql strict immutable;

CREATE OR REPLACE FUNCTION use_foo()
RETURNS void AS $$
DECLARE r record AS $$
BEGIN
r := foo(10,20);
RAISE NOTICE '% %', r.c, r.d;
END;
$$ LANGUAGE plpgsql immutable;

SELECT use_foo();

Regard

Pavel Stehule

2010/7/14 Bill Thoen <bthoen(at)gisnet(dot)com>:
> I'm having some difficulty getting plpgsql to recognize a function with a
> couple of OUT parameters. I'm either declaring the function incorrectly,
> making the call to it in the wrong way or my program is simply possessed by
> evil spirits. I'm using Postgres 8.1.5.
> What appears  to be happening is that it's declaring the function as if it
> returned  a record and had only two  parameters, but I'm trying to call it
> with four parameters, with two of them being OUT parameters. So the compiler
> sees two different versions of the function and refused to do anything more.
> The example below shows the problem, but it's just something to exercise the
> function calls and generate the error. Can anyone spot the screw-up in this
> little example? (the error message is listed below in the block comment)
> TIA,
> -Bill Thoen
>
> CREATE OR REPLACE FUNCTION fishy( s1 text, s2 text, OUT n integer, OUT f
> real ) AS $$
> DECLARE
>  c integer;
> BEGIN
>  c := length( s1 );
>  n := length( s1 || s2 );
>  f  := c::real / n::real;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION main() RETURNS VOID AS $$
> DECLARE
>  str1 text;
>  str2 text;
>  num integer := 0;
>  fnum real := 0.0;
> BEGIN
>  str1 := 'One fish, two fish';
>  str2 := 'Shark fish, No fish';
>  SELECT fishy( str1, str2, num, fnum) ;
>
>  RAISE NOTICE 'fishy() analysis: %  %', num, fnum;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT main();
>
> /*  ERROR MESSAGE
>
> psql:ex_out_fail.sql:28: ERROR:  function fishy(text, text, integer, real)
> 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  fishy(  $1 ,  $2 ,  $3 ,  $4 )"
> PL/pgSQL function "main" line 9 at SQL statement
>
> And when I run \df from the pgsql command line, it shows up like this:
>  | fishy            | record                | text, text
>
> */
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>