Thread: Any way to use refcursors from python?

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?

Thanks,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, Dec 29, 2010 at 1:46 PM, Andrew Sullivan <ajs@crankycanuck.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

On Wed, Dec 29, 2010 at 03:05:26PM +0100, Daniele Varrazzo wrote:
> There is support for named cursors instead: if you use:

Yeah, this I got.  But. . .

> 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.

. . .unless I have badly misunderstood how things work (usual
disclaimer applies!), that doesn't help on a large set returned by a
function.  I _think_ if I have a big set returned from an SRF, then
I'll have to marshall the entire set first and then the cursor gets
declared over that.  It's exactly this behaviour I think I'm trying to
avoid.

To make this a little more concrete: I have a bunch of nicely
normalized tables with several one-to-many relations.  Unfortunately,
the target system actually needs these one-to-many relations formatted
as a single row, with arrays for the many values.  What I was hoping
to do was build a nice function that could do all that work, and step
through it.  But it seems that instead I have to get the entire result
set.  In one use case, this result set ought to be 10,000 or so rows
each time, which doesn't seem like the sort of thing I want to fetch
in a single go.  I'd also prefer not to do this in a loop one row at a
time, because I think that will be inefficient.

(It could be that a view is what I really want.  I'm exploring the
performance consequences.)

Thanks,

A

--
Andrew Sullivan
ajs@crankycanuck.ca