Re: Function call with offset and limit

On Thu, Dec 22, 2005 at 11:18:22AM +0100, Martijn van Oosterhout wrote:
> On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote:
> > Hi
> >
> > I've just tried it, and it works. So it's a good work-around.
> >
> > Though, is it a wanted feature to have a function being performed on
> > each row before the offset ?
>
> Well, saying offset 5000 pretty much means to calculate the first 5000
> rows and throw away the result. To calculate that it needs to execute
> the function each time. What happens if the function has side-effects
> like in your case? What if you had a WHERE clause that depended on the
> result of that function?
>
> If the function has no side-effects, like say pow() then the backend
> could skip but that should be transparent to the user. SQL allows you
> specify the way you want it and PostgreSQL is simply executing what you
> wrote down...

Well, it would be a good optimization to make if the function is
immutable and isn't otherwise referenced (ie: by WHERE or ORDER BY),
there's no reason I can think of to execute it as you read through the
rows. Might be able to do this with STABLE functions as well.

TODO?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461