Thread: plpqsql and RETURN NEXT requires a LOOP?
<p align="LEFT"><span lang="en-us"><font face="Arial" size="2">From my reading of</font></span><span lang="en-us"></span><spanlang="en-us"> <font face="Arial" size="2">36.7.1 Returning from a Function</font></span><p align="LEFT"><spanlang="en-us"></span><a href="http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING"><span lang="en-us"></span><spanlang="en-us"><u><font color="#0000FF" face="Arial" size="2">http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING</font></u></span><span lang="en-us"></span></a><spanlang="en-us"></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">it appears that</font></span><span lang="en-us"></span><span lang="en-us"> <font face="Arial" size="2">RETURNNEXT</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2"> in a plpgsql functionrequires you to loop through the result set. Is this correct? If so, I would be happy to post this example to theinteractive docs</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2"> (which could usea RETURN NEXT example)</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2">, but wantedto make sure</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2"> that I wasn</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">t missing something more elegant</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2"> or more efficient</font></span><span lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">.</font></span><p align="LEFT"><span lang="en-us"><fontface="Arial" size="2">Best Regards,</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">Robert Davidson</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">-----------------------------------------</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">CREATETABLE test (textcol varchar(10), intcol int);</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">INSERT INTO test VALUES ('a', 1);</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">INSERT INTO test VALUES ('a', 2);</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">INSERT INTO test VALUES ('b', 5);</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">INSERT INTO test VALUES ('b', 6);</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"size="2">CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$</font></span><p align="LEFT"><spanlang="en-us"> <font face="Arial" size="2">DECLARE</font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">rec RECORD;</font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">BEGIN</font></span><p align="LEFT"><span lang="en-us"> <fontface="Arial" size="2">FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP</font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">RETURN NEXT rec;</font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">END LOOP;</font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">RETURN;</font></span><p align="LEFT"><span lang="en-us"> <fontface="Arial" size="2">END;</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">$$</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">LANGUAGE plpgsql;</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">SELECT * FROM ReturnNexting('a');</font></span><spanlang="en-us"></span><span lang="en-us"></span>
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/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING it appears that RETURN NEXT in a plpgsql function requires you to loop through the result set. Is this correct? If so, Iwould be happy to post this example to the interactive docs (which could use a RETURN NEXT example), but wanted to makesure 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');