Обсуждение: 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