Re: Any way to use refcursors from python?

On Wed, Dec 29, 2010 at 1:46 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> Hi all,
>
> For a little application I'm working on (written in Python), I have a
> number of potentially large result sets that I'd like to return from a
> PL/pgSQL function.  My natural inclination would be to return a
> refcursor.  It seems, however, that psycopg2 can't accept such
> references.
>
> Have I completely overlooked something (there would be no news in
> that, of course)?  Is there some other interface I ought to be using?

I don't think there is direct support for PL/pgSQL refcursors... not
that I know at least.

There is support for named cursors instead: if you use:

curs = conn.cursor("MYNAME")
curs.execute("SOME SQL")

this will result in a query similar to:

declare MYNAME cursor for SOME SQL

and the curs.fetch*() methods will result in FETCH commands instead of
just client side manipulations. So there may be some small sql you may
execute (may it be "select * from my_function()"? -- don't know the
syntax to interact with refcursors) to bind the refcursor to a named
cursor.

Failing that, because there is already support for FETCH in named
cursor, it may be easy enough to add refcursors support to the
library.

Feedback is appreciated, either to start designing a refcursors
feature to the library or to mention the named cursors trick in the
docs if it works.

Regards,

-- Daniele