Re: functions with side effect

On 07/19/2018 09:43 AM, Torsten Förtsch wrote:
> On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
>      > Hi,
>      >
>      > assuming
>      >
>      > SELECT nextval('s'), currval('s');
>      >
>      > or
>      >
>      > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>      >
>      > is there any guarantee that the 2 output values are the same?
> 
>     Assuming you are only working in single session:
> 
>     https://www.postgresql.org/docs/10/static/functions-sequence.html
> 
>     "currval
> 
>           Return the value most recently obtained by nextval for this
>     sequence in the current session. (An error is reported if nextval has
>     never been called for this sequence in this session.) Because this is
>     returning a session-local value, it gives a predictable answer whether
>     or not other sessions have executed nextval since the current
>     session did."
> 
> 
> I know that. My question was about the execution order of f1 and f2 in 
> "SELECT f1(), f2()". In theory they can be executed in any order. But 
> since the side effect in nextval determines the result of currval, I am 
> asking if that order is well-defined or considered an implementation 
> detail like in C.
> 

To eliminate plan caching:

DO
$$
DECLARE
     rs record;
BEGIN
     FOR i IN 1..1000 LOOP
         EXECUTE 'SELECT  nextval($1), currval($1)' INTO rs USING 
'order_test';
         RAISE NOTICE 'Currval is %', rs.currval;
     END LOOP;
END$$;

It still works over multiple runs, even when bumping LOOP counter to 
100,000.

-- 
Adrian Klaver
adrian.klaver@aklaver.com