This issue is on PostgreSQL 8.4.1 under CentOS 5.3 x86_64.
I have a scroll cursor defined like so:
source_host SCROLL CURSOR IS
SELECT ....;
Inside my PL/PGSQL stored procedure, I am opening the cursor like so:
OPEN source_host;
FETCH source_host INTO src_host;
result.source_host_refcurs := source_host;
...
blah blah blah
....
RETURN result;
Then, I execute the stored procedure like this:
SELECT * FROM MyStoredProc(blah);
FETCH ALL FROM source_host;
The stored procedure returns a complex data type (result) with a refcursor set up as source_host. When I use the "FETCH ALL" syntax, I get no results. However, if I use any of these, I get the one and only record that is returned:
FETCH FIRST FROM source_host;
FETCH LAST FROM source_host;
FETCH ABSOLUTE 1 FROM source_host;
Any of these fail:
FETCH NEXT
FETCH PRIOR
FETCH RELATIVE x where x is any number
FETCH x where x is any number
FETCH ALL
FETCH FORWARD
FETCH FORWARD x where x is any number
FETCH FORWARD ALL
FETCH BACKWARD
FETCH BACKWARD x where x is any number
FETCH BACKWARD ALL
Now, if I comment out the 'FETCH source_host INTO src_host' line inside the stored procedure, then ALL of these work:
FETCH FIRST
FETCH LAST
FETCH ABSOLUTE x
FETCH RELATIVE x
FETCH NEXT
FETCH ALL
FETCH FORWARD
FETCH FORWARD x
FETCH FORWARD ALL
FETCH x
I have attempted to perform a MOVE FIRST aftering doing the 'FETCH source_host INTO src_host' line, as well as MOVE LAST, MOVE ABSOLUTE 1, etc. No attempt at doing a MOVE allows the FETCH ALL and the like to work. Only FETCH FIRST, FETCH LAST, and FETCH ABSOLUTE seem to work after I have touched the cursor inside the stored procedure. In fact, I can remove the 'FETCH source_host INTO src_host' line and replace it with a MOVE statement and it results in the same problem.
I absolutely need to have FETCH ALL working. I don't care about anything else other than FETCH ALL. I actually have about 10 cursors that are returned like this from the stored procedure, and most of them have several dozen records that need to be retrieved. I execute a single transaction where I run the stored procedure and fetch all results all at once. This was working just fine a couple of days ago. Not sure what broke.
If anyone has any ideas on what might be going wrong here, I would really appreciate some assistance.
Thanks in advance.
--
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
