Re: Functions that return both Output Parameters and recordsets

Hello,

I forgot, You can do it via recordset of cursors.

http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html
37.8.3.3. Returning Cursors

one cursor returns TotalRecords and TotalPages columns and second
record returns searchResult.

Regards
Pavel



2007/6/11, Jeremy Nix <Jeremy.Nix@sfsltd.com>:
> I see what you're doing, but I'm not quite sure how to adapt it to what
> I'm doing.  Here's simplified snippet of my code.  Can elaborate on how
> I can return a recordset and the output parameters.?
>
> CREATE OR REPLACE FUNCTION Search (OUT TotalRecords int, OUT TotalPages int)
> RETURNS SETOF record AS
> $BODY$
>     TotalRecords := 10;
>     TotalPages := 1;
>
>     FOR myRecord IN
>         SELECT cols FROM searchResults
>     LOOP
>         RETURN NEXT myRecord;
>     END LOOP;
>
> Thanks,
>
> __________________________________
> Jeremy Nix
> Senior Application Developer
> Southwest Financial Services, Ltd.
> (513) 621-6699
>
>
>
> Pavel Stehule wrote:
> > Hello
> >
> > it's possible, but it's probably some different than you expect
> >
> >
> > CREATE OR REPLACE FUNCTION foo(OUT a integer, OUT b integer)
> > RETURNS SETOF RECORD AS $$
> > BEGIN
> >  a := 10; b := 10;
> >  RETURN NEXT;
> >  a := 11; b := 20;
> >  RETURN NEXT;
> >  RETURN;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > postgres=# select * from foo();
> > a  | b
> > ----+----
> > 10 | 10
> > 11 | 20
> > (2 rows)
> >
> > Regards
> > Pavel Stehule
> >
> >
> > 2007/6/11, Jeremy Nix <Jeremy.Nix@sfsltd.com>:
> >> Is this possible?  I'm attempting to create a function like this and I'm
> >> getting the following error:
> >>
> >> ERROR: RETURN NEXT cannot have a parameter in function with OUT
> >> parameters at or near "myRecord".
> >>
> >> --
> >>
> >> __________________________________
> >> Jeremy Nix
> >> Senior Application Developer
> >> Southwest Financial Services, Ltd.
> >> (513) 621-6699
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: Have you checked our extensive FAQ?
> >>
> >>                http://www.postgresql.org/docs/faq
> >>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>