On 11/3/06, Richard Troy <rtroy@xxxxxxxxxxxxxxxx> 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
