[HACKERS] Interesting issue with SFR in PL/pgSQL ...
I am about to port a large database application from 7.4.x* to 8.0 (mainly to test 8.0). There is an interesting thing I have come across: CREATE OR REPLACE FUNCTION xy(int4) RETURNS SETOF RECORD AS ' DECLARE v_isALIAS FOR $1; v_loop int4; v_rec RECORD; BEGIN v_loop := 0; SELECT INTO v_rec 0; WHILE (v_loop v_is) LOOP SELECT INTO v_rec v_loop; v_loop := v_loop + 1; RETURN NEXT v_rec; END LOOP; RETURN NEXT v_rec; RETURN v_rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM xy(0) AS (id int4); SELECT * FROM xy(1) AS (id int4); This function works nicely in 7.4.x (even without the last RETURN NEXT). 8.0 returns an error. [EMAIL PROTECTED] tmp]$ psql microtec -h epi t.sql ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near v_rec at character 324 ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 7.4.1 works nicely ... [EMAIL PROTECTED] tmp]$ psql microtec -h epi -p t.sql CREATE FUNCTION id 0 (1 row) id 0 0 (2 rows) I have tried to look it up in the source code (gramy.y line 1144) but I don't really know what the new check which has been added in 8.0 is good for. Can anybody give me a hint how it is supposed to be? Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interesting issue with SFR in PL/pgSQL ...
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: CREATE OR REPLACE FUNCTION xy(int4) RETURNS SETOF RECORD AS ' ... RETURN v_rec; ... ' LANGUAGE 'plpgsql'; ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near v_rec at character 324 You were never supposed to do that, although prior releases did not check for the mistake. See http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING : When a PL/pgSQL function is declared to return SETOF sometype, the : procedure to follow is slightly different. In that case, the individual : items to return are specified in RETURN NEXT commands, and then a final : RETURN command with no argument is used to indicate that the function : has finished executing. ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 8.0 not only detects the error, but does so during CREATE FUNCTION (because it's treated as a syntax error). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
