[HACKERS] Does setof record in plpgsql work well in 7.3?
Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; RETURN NEXT rec; END LOOP; RETURN null; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); 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 ... One can also create your own return type in the following manner. create type my_return_type as ( foo integer, bar text ); Now, the declare block has the following:- DECLARE rec my_return_type%ROWTYPE The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF my_return_type ... Regards, Grant Finnemore Masaru Sugawara wrote: Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
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])
