How to Declare Functions Containing OUT PArameters?

Поиск
Список
Период
Сортировка
От Bill Thoen
Тема How to Declare Functions Containing OUT PArameters?
Дата
Msg-id 4C3E0045.6040701@gisnet.com
обсуждение исходный текст
Ответы Re: How to Declare Functions Containing OUT PArameters?  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: How to Declare Functions Containing OUT PArameters?  ("Joshua D. Drake" <jd@commandprompt.com>)
Re: How to Declare Functions Containing OUT PArameters?  ("Joshua D. Drake" <jd@commandprompt.com>)
Список pgsql-general
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

*/


В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Any ideas on Version 9.0 production release date?
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: constraint/rule/trigger - insert into table X where not in table Y