Re: Counting records in a PL/pgsql cursor

On Thu, Nov 02, 2006 at 10:43:58PM +0100, Magnus Hagander 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.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.