plpqsql and RETURN NEXT requires a LOOP?

From: "Davidson, Robert" <robdavid(at)amazon(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: plpqsql and RETURN NEXT requires a LOOP?
Date: 2006-03-21 17:51:09
Message-ID: 8333C841129E074E9F83FC80676BA76E0BE666@exchg-sea3-03.ant.amazon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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');

Browse pgsql-sql by date

  From Date Subject
Next Message Owen Jacobson 2006-03-21 17:54:48 Re: plpqsql and RETURN NEXT requires a LOOP?
Previous Message Daniel Caune 2006-03-21 16:13:58 Re: Power cut and performance problem