Re: functions with side effect

On Thu, Jul 19, 2018 at 8:10 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123(at)gmail(dot)com> writes:
> > 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.
>
> The current implementation evaluates select-list items left to right.
> I doubt we'd be eager to change that, since there are surely many
> applications that depend on that behavior, whether it's formally specified
> or not. But elsewhere in a query than the select target list, there are
> no guarantees, and there's lots of precedent for whacking around the
> evaluation order in e.g. WHERE.
>
> I'd be a little more wary with examples like your other one:
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> since there's an additional unspecified question there, which is
> whether the planner will "flatten" the sub-select. To put it more
> clearly, you'd be taking big risks with
>
> SELECT y, x FROM (VALUES (nextval('s'), currval('s'))) t(x, y);
>
> Right now it seems the nextval is done first, but I would not want to bet
> on that staying true in the future. [ experiments some more ... ]
> Actually, looks like we have a rule against flattening sub-selects whose
> targetlists contain volatile functions, so maybe you'd get away with that
> for the indefinite future too.
>

Thanks, this was actually a part of an insert statement I found in our
code. Something like

INSERT INTO ...
VALUES (nextval(), ..., 'string' || currval())

Just to be on the safe side I changed it to

INSERT INTO ...
SELECT next.id, ..., 'string' || next.id
FROM nextval() next(id)