Search Postgresql Archives

Performing FETCH ALL from a SCROLL CURSOR failing to return results

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux