On Sun, 29 Sep 2002 13:42:43 +0200
Grant Finnemore <grantf@guruhut.co.za> wrote:
> Note the use of the "RETURN NEXT rec" line in the body
> of the for loop, and also the "RETURN null" at the end.
>
> It is also possible to create typed returns, so in this
> case, in the declare body, the following would be valid.
> DECLARE
> rec test%ROWTYPE;
>
> The function definition then becomes:-
> CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ...
Thank you for your useful info. the previous function turned out to work
correctly by using "RETURN NEXT rec." And, I found out that plpgsql was
able to nest one.
-- for example
CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec1 record; rec2 record; rec3
record;BEGIN SELECT INTO rec1 max(a) AS max_a FROM test; FOR rec2 IN SELECT * FROM test WHERE a = $1 LOOP
SELECT INTO rec3 * FROM (SELECT 1::integer AS a, ''test''::text AS b) AS t; RETURN NEXT rec3;
rec2.a = rec2.a + rec3.a + rec1.max_a; RETURN NEXT rec2; END LOOP; RETURN NEXT rec3; RETURN;
END;
' LANGUAGE 'plpgsql';
SELECT * FROM myfunc(1) AS t(a integer, b text);
a | b
---+------------1 | test5 | function11 | test5 | function111 | test
(5 rows)
Regards,
Masaru Sugawara
