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_is ALIAS 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.
[hs@fedora 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 ...
[hs@fedora tmp]$ psql microtec -h epi -p 9999 < 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
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> 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