Re: using server side cursor

thanks for your helpful answers.

>> mycursor.execute ( "fetch mycursor" )
is working okay.

you are right : using the psycopg native support for cursors makes
clearer code. But, yes indeed, the pgsql function is somewhat a
mandatory API to the database, so the Python code doesn't have to know
the inner query structure.

Daniele, beside the hack you are providing, you say : "all cursors
from the same connections live in the same transaction"
=> is it something specific that is true today but may change in the
future ? I mean, may I rely on this for a long-living code ?

thanks again
Tom


2011/10/14 Daniele Varrazzo <daniele.varrazzo@gmail.com>:
> On Fri, Oct 14, 2011 at 12:41 PM, Federico Di Gregorio <fog@dndg.it> wrote:
>> On 14/10/11 12:32, thomas veymont wrote:
>> [snip]
>>> I guess I'm doing it the wrong way. There's a doc in psycopg
>>> about named cursors but I don't understand exactly how I should
>>> follow it to tie to my problem. (that is : using my existing pgpsql function
>>> that returns a cursor, then iterate on that cursor).
>>>
>>> any suggestion ?
>>
>> Yes, use the native support for server-side cursors in psycopg. First,
>> don't use a procedure to setup the cursor but just pass to execute()
>> your SQL:
>
> He has a point though: what if somebody has a database function call
> to be used as interface?
>
> Oh, there's a nice hack that can be done :P because all cursors from
> the same connections live in the same transaction, one can use a
> regular cursor to create the postgres refcursor and a named cursor to
> iterate it:
>
>>>> cur1 = cnn.cursor()
>>>> cur1.callproc('myfunction', ['mycursor'])
> ['mycursor']
>
>>>> cur2 = cnn.cursor('mycursor')
>>>> cur2.fetchone()
> (1,)
>>>> cur2.fetchmany(2)
> [(2,), (3,)]
>
> Nasty :D
>
>
> -- Daniele
>