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



Sat, 18 Sep 2004 13:17:40 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Interesting issue with SFR in PL/pgSQL ...
=?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

Sat, 18 Sep 2004 11:13:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Interesting issue with SFR in PL/pgSQL ... 