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@elma.fr>
> Elma
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 

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