Re: Function call with offset and limit

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
>

--
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