Re: Does setof record in plpgsql work well in 7.3?

On Sun, 29 Sep 2002 13:42:43 +0200
Grant Finnemore <grantf(at)guruhut(dot)co(dot)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 | test
5 | function1
1 | test
5 | function11
1 | test
(5 rows)

Regards,
Masaru Sugawara