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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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, 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'); ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
