Hi All
I have a quite strange problem with RETURN NEXT statement.
I have a big table with 500 millions of rows running on Postgres 8.0. 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")
I actually wrote some procedures in PL/SQL using dynamical queries,
and once I obtained the following error.
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 8 at return next
To solve the problem, I used just the following simple PL/SQL functions, and
a query "select * from yyy()"
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; RETURN NEXT rec; EXIT WHEN NOT FOUND;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
I was quite surprised by this errors, because I have tried the same
functions on rather same (but smaller table) on Postgres 7.4.6 on my laptop
without any problems.
For debugging purposes, I just have created by hand on Postgres 8.0 machine
the small table "q3c" with just two rows, but same structure as usno table. 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")
And, after replacing "usno"->"q3c" in the xxx() and yyy(), the query
"select * from yyy()" worked without problems!!!
So, how can it be, that my PL/SQL functions works fine on one(smaller)
table, but fails on another(bigger) table.
Thanks in advance for any ideas. Sergey
PS
I have tried my code replacing the declaration
"rec record;" by "rec TABLE_NAME%ROWTYPE", and it worked for both (big and
small table), but I don't understand, why it doesn't work with the type
"record".
------------------------------------------------------------
Sergey E. Koposov
Sternberg Astronomical Institute, Moscow University (Russia)
Max-Planck Institute for Astronomy (Germany)
Internet: math@sai.msu.ru, http://lnfm1.sai.msu.su/~math/