On 11/3/06, Magnus Hagander <mha@xxxxxxxxxxxxxx> wrote:
> > Is there any way to count how many hits I got in a cursor > in PL/pgsql? > > > > I have a function that will "window" through the result of > a (large) > > query based on two parameters, but I also want to return > the number of > > hits to the client. Right now I'm looping through the entire cursor > > and incrementing a local variable, which I later return (along with > > the first <n> records in the resultset) to the client. But > this seems > > horribly inefficient... I'd just like to ask "how many rows are in > > this cursor", is there a way to do that without looping > through them all? > > You can move to the end, look at the row number, then move to > the beginning. It will still need to materialise the entire > resultset though. How do I do that? remember this is a pL/pgsql cursor. From what I can find at http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html#PLPGSQL-C URSOR-USING, I can only do FETCH to get the next row, or CLOSE. I can deal with materializing the resultset, but I want to get away from the loop-a-thousand-times-doing-plus-one...
i dont think its possible. note that you can make a refcursor inside your plpgsql function and pass it to an sql function which can do sql cursor operations on it -- i think :-)..haven't tried it yet. merlin
