Обсуждение: Re: Returning multiple rows in 8.4

On 19:59,  wrote:
> how can I write a function to return multiple rows? ( in plpgsql )

Yep, it's all in the docs:

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

> what is the return value of the function? records?

Depends on how you declare the function. There's an implicit type
declared for each table and view, so you can have your function return
SETOF users -

   create or replace function my_function(....)
   returns setof users
   as ... (etc)

- and then the returned set will be rows of the users table.

Alternatively, you could declare a custom type and return a SETOF that
instead.

Finally, you can use OUT parameters to return multiple values from the
function - this is handy if you need to return just a few values.


Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 09/11/2009 22:43, Raymond O'Donnell wrote:
> On 19:59,  wrote:
>> how can I write a function to return multiple rows? ( in plpgsql )
>
> Yep, it's all in the docs:
>
> http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

[snip]

> Finally, you can use OUT parameters to return multiple values from the
> function - this is handy if you need to return just a few values.

Just to clarify further - this last option returns just a single row.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Mon, Nov 09, 2009 at 11:23:52PM +0000, Raymond O'Donnell wrote:
> On 09/11/2009 22:43, Raymond O'Donnell wrote:
> > Finally, you can use OUT parameters to return multiple values from the
> > function - this is handy if you need to return just a few values.
>
> Just to clarify further - this last option returns just a single row.

You can make it return several pretty easily:

  create function foo(out i text, out j text) returns setof record
      language plpgsql as $$
    begin
      i := 1; j := 2;
      return next;
      i := 2; j := 5;
      return next;
    end; $$;

--
  Sam  http://samason.me.uk/