Обсуждение: Performing FETCH ALL from a SCROLL CURSOR failing to return results

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

Eliot Gable <egable+pgsql-general@gmail.com> writes:
> Inside my PL/PGSQL stored procedure, I am opening the cursor like so:

> OPEN source_host;
> FETCH source_host INTO src_host;

So you already fetched the one row in the cursor.  FETCH ALL really
means "fetch all the rest of the rows", which is why it returns nothing.
The other variants you mention will reposition the cursor so they can
re-read the row.

> I absolutely need to have FETCH ALL working.

It is working.  What you're not doing correctly is repositioning the
cursor to before the first row.  Try MOVE BACKWARDS ALL.

            regards, tom lane