Re: BUG #14059: BUG? function in select clause called more times when use offset - Mailing list pgsql-bugs

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