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

On 10/21/2013 3:52 PM, BladeOfLight16 wrote:
> 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.

indeed. ran into someone who was calling pg_stop_backup() deep in a
heavily nested query+function call mess, totally blew my mind why anyone
would think that was a good idea.

--
john r pierce 37N 122W
somewhere on the middle of the left coast