Re: Counting records in a PL/pgsql cursor

On 11/3/06, Richard Troy <rtroy(at)sciencetools(dot)com> wrote:
> On Fri, 3 Nov 2006, Merlin Moncure wrote:
> > >
> > > 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
>
> ...If you know your application well enough, you might get away with doing
> a select count() with the same where clause just before entering the
> cursor. It _could_ of course be wrong, though! OTOH, it would be much
> faster. If the only down-side is occasionally giving users an incorrect
> count, then perhaps call it a "row estimate", and let them marvel at how
> accurate the estimate is most of hte time!

you could guarantee correctness by doing serializable transations. or
by locking the resources in question. however if the non-trivial
portions of the query can't be optimized out in a count(*), this is
pretty much a no-go cause you have to do everything twice...

merlin