Strange RETURN NEXT behaviour in Postgres 8.0
От | Sergey E. Koposov |
---|---|
Тема | Strange RETURN NEXT behaviour in Postgres 8.0 |
Дата | |
Msg-id | Pine.LNX.4.44.0502121929520.29807-100000@lnfm1.sai.msu.ru обсуждение исходный текст |
Ответы |
Re: Strange RETURN NEXT behaviour in Postgres 8.0
|
Список | pgsql-hackers |
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/
В списке pgsql-hackers по дате отправления: