PostgreSQL SQL - plpqsql and RETURN NEXT requires a LOOP?

Author

plpqsql and RETURN NEXT requires a LOOP?

Davidson, Robert

2006-03-21, 1:32 pm

From my reading of 36.7.1 Returning from a Function
http://www.postgresql.org/docs/8.1/...MENTS-RETURNING

it appears that RETURN NEXT in a plpgsql function requires you to loop through the result set. Is this correct? If so, I would be happy to post this example to the interactive docs (which could use a RETURN NEXT example), but wanted to make sure that I wasn't missing something more elegant or more efficient.

Best Regards,

Robert Davidson
-----------------------------------------

CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);

CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM ReturnNexting('a');

Owen Jacobson

2006-03-21, 1:32 pm

In general, to do anything useful with RETURN NEXT you need a loop. However, it doesn't need to be a loop over another resultset: you can do a computation in a loop, returning values as you go.

Excuse the outlook-ism.

-Owen

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 9:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] plpqsql and RETURN NEXT requires a LOOP?

From my reading of 36.7.1 Returning from a Function
http://www.postgresql.org/docs/8.1/...MENTS-RETURNING
it appears that RETURN NEXT in a plpgsql function requires you to loop through the result set. Is this correct? If so, I would be happy to post this example to the interactive docs (which could use a RETURN NEXT example), but wanted to make sure that I wa
sn't missing something more elegant or more efficient.
Best Regards,
Robert Davidson
-----------------------------------------
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);
CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM ReturnNexting('a');

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links