Strange RETURN NEXT behaviour in Postgres 8.0

From: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Strange RETURN NEXT behaviour in Postgres 8.0
Date: 2005-02-12 17:10:46
Message-ID: Pine.LNX.4.44.0502121929520.29807-100000@lnfm1.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sai(dot)msu(dot)ru, http://lnfm1.sai.msu.su/~math/

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Markus Bertheau 2005-02-13 01:50:00 Re: prev main developer page
Previous Message Gilles 2005-02-12 15:06:01 Urgent problem: Unicode characters greater than or equal to 0x10000 are not supported