Re: Function call with offset and limit

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 ?

Le mercredi 21 décembre 2005 à 13:41 -0600, Jim C. Nasby a écrit :
> Have you tried
>
> SELECT *, test_func(idkeyword)
> FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
> ;
>
> ?
>
> This should probably have been on -general, btw.
>
> On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
> > Hi all,
> >
> > We need to find a solution for a strange problem.
> > We have a plpgsql FUNCTION which performs an heavy job (named
> > test_func).
> >
> > CREATE or replace function test_func(z int) returns integer as $$
> > declare
> > tst integer;
> > begin
> > --
> > -- Large jobs with z
> > --
> > tst := nextval('test_truc');
> > return tst;
> > end;
> > $$ LANGUAGE plpgsql;
> >
> >
> > So I made this test:
> >
> > test=# select setval('test_truc',1);
> > setval
> > --------
> > 1
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 1
> > (1 row)
> >
> > test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
> > idkeyword | test_func
> > -----------+-------------
> > 5001 | 5002
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 5002
> > (1 row)
> >
> >
> > This demonstrates that the function is called 5001 times though only one
> > row is returned. Problem is that this heavy job is performed much, much
> > more than needed.
> >
> > But, If I do:
> > test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
> > My function is called only once.
> >
> > Is there any work around ?
> >
> >
> > Thanks
> > --
> > REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr>
> > Elma
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
--
REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr>
Elma