Обсуждение: Functions that return both Output Parameters and recordsets
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
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 >
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
>>
Hello it's not possible. PostgreSQL doesn't support multiple recordset. You have to have two functions. 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/ >
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/ >
Pavel Stehule escribió: > Hello > > it's not possible. PostgreSQL doesn't support multiple recordset. You > have to have two functions. The other idea is to declare the function to return SETOF refcursor (or have an OUT refcursor param), and return two refcursors open with the different recordsets. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mon, Jun 11, 2007 at 03:20:15PM +0200, Pavel Stehule wrote: > it's not possible. PostgreSQL doesn't support multiple recordset. You > have to have two functions. If you don't mind handling cursors then you could return multiple cursors from one function. See the PL/pgSQL documentation for an example (the example is at the bottom of the page). http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html -- Michael Fuhr
Jeremy Nix <Jeremy.Nix@sfsltd.com> writes:
> 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.?
I suppose what you need is something like
CREATE OR REPLACE FUNCTION Search (OUT TotalRecords int, OUT TotalPages int)
RETURNS SETOF record AS
$BODY$
FOR myRecord IN
SELECT cols FROM searchResults
LOOP
TotalRecords := myRecord.TotalRecords;
TotalPages := myRecord.TotalPages;
RETURN NEXT;
END LOOP;
Anyway the point is that when you are using OUT parameters you do not
say anything in RETURN or RETURN NEXT. Whatever you last assigned to
the parameter variables is what's returned.
regards, tom lane