Re: Strange RETURN NEXT behaviour in Postgres 8.0

> > For the real functions which I use, instead of
>
> > query = ''SELECT * FROM usno'';
>
> > I have
>
> > query = my_C_function(some_args);
>
> Oh? I'd make a small side bet that the underlying error is in your C
> function --- possibly it's tromping on some data structure and the
> damage doesn't have an effect till later. If you can demonstrate the
> problem without using any custom C functions then I'd be interested to
> see a test case.

I want to clarify, that I have a problem even without my C functions!!

And show the full exact(but long) test case, which I performed just now
specially.

I begin from table usno with 500 millions records

wsdb=# \d usno
Table "public.usno"
Column | Type | Modifiers
--------+--------+-----------
ra | real |
dec | real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box |
Indexes:
"box_ind" rtree (errbox)
"ipix_ind" btree (ipix)
"radec_ind" btree (ra, "dec")

The declaration of the functions:

CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM usno'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF usno AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;

wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 10 at return next

We see the error

#############################################

Now with q3c table instead of unso

wsdb=# \d q3c
Table "public.q3c"
Column | Type | Modifiers
--------+--------+-----------
ra | real |
dec | real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box |
Indexes:
"box_ind1" rtree (errbox)
"ipix_ind1" btree (ipix)
"radec_ind1" btree (ra, "dec")

That table is not empty but filled by random numbers

wsdb=# select * from q3c;
ra | dec | bmag | rmag | ipix | errbox
----+-----+------+------+------+-------------
3 | 3 | 4 | 5 | 55 | (5,6),(3,4)
4 | 5 | 6 | 5 | 33 | (3,4),(1,2)
(2 rows)

Now the changed functions (notice, the only difference is
replacing all occurencies of "usno" to "q3c")

CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM q3c'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF q3c AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;

wsdb=# drop FUNCTION yyy();
DROP FUNCTION
wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ra | dec | bmag | rmag | ipix | errbox
----+-----+------+------+------+-------------
3 | 3 | 4 | 5 | 55 | (5,6),(3,4)
4 | 5 | 6 | 5 | 33 | (3,4),(1,2)
(2 rows)

We don't see the error. But the only change was the change from one big
table to a smaller one with the precisely same structure.

###########################################