Обсуждение: Creating a PL/pgSQL function that returns multiple out parameters and refcursor

Поиск
Список
Период
Сортировка

Creating a PL/pgSQL function that returns multiple out parameters and refcursor

От
Néstor Boscán
Дата:
Hi

Ho do I create a PL/pgSQL function that returns multiple out parameters and a refcursor. Something like this:

create function myfunction(p_cursor out refcursor, p_code out varchar, p_message out varchar) returns record as $$
  begin
    open p_cursor is select * from table;

    p_code := 'OK';
    p_message := 'message';

    return ???
  end;
$$ language plpgsql;

Re: Creating a PL/pgSQL function that returns multiple out parameters and refcursor

От
Michael Paquier
Дата:
On Fri, Oct 3, 2014 at 1:15 AM, Néstor Boscán <nestorjb@gmail.com> wrote:
> Ho do I create a PL/pgSQL function that returns multiple out parameters and
> a refcursor.

Using a plain "RETURN;" is just but fine, your output parameters are
being set internally in the function:
=# create function myfunction(id_base in int, id1 out int, id2 out varchar)
returns record as $$
  begin
    id1 := id_base * 2;
    id2 := id_base * 3;
    return;
  end;
$$ language plpgsql;
CREATE FUNCTION
=# select * from myfunction(3);
 id1 | id2
-----+-----
   6 | 9
(1 row)
=# select * from myfunction(4);
 id1 | id2
-----+-----
   8 | 12
(1 row)

Regards,
--
Michael