Обсуждение: BUG #14059: BUG? function in select clause called more times when use offset

The following bug has been logged on the website:

Bug reference:      14059
Logged by:          digoal
Email address:      digoal@126.com
PostgreSQL version: 9.5.2
Operating system:   CentOS 6.x x64
Description:

postgres=# create or replace function f() returns void as $$
declare
begin
  raise notice 'called';
end;
$$ language plpgsql strict volatile;
CREATE FUNCTION

postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset
3 limit 2;
NOTICE:  called
NOTICE:  called
NOTICE:  called
NOTICE:  called
NOTICE:  called
 f | id
---+----
   |  4
   |  5
(2 rows)

offset skip 3 tuple, but function f() called with 5 times, can we tuning
this .
or this is a bug?

digoal@126.com writes:
> postgres=# create or replace function f() returns void as $$
> declare
> begin
>   raise notice 'called';
> end;
> $$ language plpgsql strict volatile;
> CREATE FUNCTION

> postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset
> 3 limit 2;
> NOTICE:  called
> NOTICE:  called
> NOTICE:  called
> NOTICE:  called
> NOTICE:  called
>  f | id
> ---+----
>    |  4
>    |  5
> (2 rows)

> offset skip 3 tuple, but function f() called with 5 times, can we tuning
> this .
> or this is a bug?

No, it's not a bug.  OFFSET only results in the skipped tuples not being
delivered to the client; it does not cause them not to be computed.

You could probably do something with a two-level select with the OFFSET
in the sub-select and the volatile function in the top level.

            regards, tom lane

<div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial"><br />Thanks<br /><br /><br /><br /><div
style="position:relative;zoom:1">--<br/>公益是一辈子的事,I'm Digoal,Just Do It.<br /><div style="clear:both"></div></div><div
id="divNeteaseMailCard"></div><br/><pre><br />At 2016-04-02 22:49:26, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: 
>digoal@126.com writes:
>> postgres=# create or replace function f() returns void as $$
>> declare
>> begin
>>   raise notice 'called';
>> end;
>> $$ language plpgsql strict volatile;
>> CREATE FUNCTION
>
>> postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset
>> 3 limit 2;
>> NOTICE:  called
>> NOTICE:  called
>> NOTICE:  called
>> NOTICE:  called
>> NOTICE:  called
>>  f | id
>> ---+----
>>    |  4
>>    |  5
>> (2 rows)
>
>> offset skip 3 tuple, but function f() called with 5 times, can we tuning
>> this .
>> or this is a bug?
>
>No, it's not a bug.  OFFSET only results in the skipped tuples not being
>delivered to the client; it does not cause them not to be computed.
>
>You could probably do something with a two-level select with the OFFSET
>in the sub-select and the volatile function in the top level.
>
>            regards, tom lane
</pre></div>