Re: Functions that return both Output Parameters and recordsets

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Jeremy Nix" <Jeremy(dot)Nix(at)sfsltd(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Functions that return both Output Parameters and recordsets
Date: 2007-06-11 13:27:23
Message-ID: 162867790706110627h359e9f39w5e0ba361397cc63d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)Nix(at)sfsltd(dot)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(dot)Nix(at)sfsltd(dot)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/
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Pit M. 2007-06-11 13:28:22 Re: transaction problem using cursors
Previous Message Pavel Stehule 2007-06-11 13:20:15 Re: Functions that return both Output Parameters and recordsets