Skip site navigation (1) Skip section navigation (2)

Re: Function call with offset and limit

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Function call with offset and limit
Date: 2005-12-21 19:41:46
Message-ID: 20051221194146.GC72143@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-12-21 19:51:46
Subject: Re: [pgadmin-hackers] Client-side password encryption
Previous:From: Bruce MomjianDate: 2005-12-21 19:16:41
Subject: Re: Automatic function replanning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group