hello,
I'm using pgsql cursors for making queries or large datasets.
To do this, my database already implements a plpgsql function that
return a cursor on some query.
(see http://www.postgresql.org/docs/9.0/static/plpgsql-cursors.html )
As shown in this doc, my database contains a function that
looks like this :
CREATE FUNCTION myfunction ( refcursor ) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
Then, from the db prompt, I am able to call the function
(thus creating a cursor), then fetch one row at a time :
BEGIN;
SELECT myfunction('mycursor');
myfunction
-----------------------
mycursor
(1 row)
FETCH mycursor;
(...)
FETCH mycursor;
COMMIT;
Now I want to do the same into the Python code -
naively I do something like:
mycursor.callproc ( "myfunction", [ "mycursor" ] ) # setup a cursor
in the db engine
mycursor.execute ( "fetch", [ "mycursor" ] ) # fetch row from this cursor
mycursor.fetchone()
(...)
but this returns an empty result.
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 ?
thanks
Tom