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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com