Search Postgresql Archives

Re: How to Declare Functions Containing OUT PArameters?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux