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>:
Show quoted text
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?
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:
Show quoted text
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 Stehule2007/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?
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>:
Show quoted text
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-6699Pavel 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 Stehule2007/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?---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
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>:
Show quoted text
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-6699Pavel 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 Stehule2007/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?---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
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