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