Re: Strange RETURN NEXT behaviour in Postgres 8.0 - Mailing list pgsql-hackers

> > 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.

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