Re: Bug? Function with side effects not evaluated in CTE

I've only skimmed this thread, but clearly, this is why using functions
with side effects in the middle of complex queries is a bad idea. =)
Something like SELECT func_with_side_effect(1); is probably fine, but
beyond that, put the function in the middle of a DO block or something and
actually code what you want to happen.

In terms of "expected" or "surprising" behavior, I don't think you can say
ANY behavior could be expected. SQL is designed to be declarative. When it
comes to retrieval (which is the issue originally raised since this
involves a SELECT before the modification), you tell it what you want, and
some engine figures out the best way to retrieve it. The engine is allowed
to make whatever optimizations it chooses as long as the result set is
correct. So if you really want to modify something, be explicit and don't
drop a function with side effects in the middle of a complex query like
this. God only knows what the engine will do with that.

In my opinion, the simplest and most correct way to handle this is to
document that there are no guarantees about what will happen with volatile
functions in these strange cases. PostgreSQL shouldn't have to make
guarantees about whether functions are evaluated in CTEs or what have you;
it should have the freedom to optimize those things away or not.