On Sun, 29 Sep 2002 13:42:43 +0200
Grant Finnemore <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
