David Johnston wrote: > Understood (I'm guessing there is no "global" cache but simply the > plan-level cache that gets populated each time?) > > However, in the following example the ps3(2) expression should also qualify > for this "folding" and thus the RAISE NOTICE should also appear during plan > time for the same reason; which, per the OP, it does not. > > pgb=# select coalesce( ps3(1), ps3(2) ); > WARNING: Call ps3(1)=1 > coalesce > ---------- > 1 > (1 row) > > It would seem the addition of the sub-select messes with the COALESCE logic. > If the function call is directly a part of the COALESCE statement it can be > optimized away by the COALESCE logic but if it is buried within a SELECT > statement the planner does not know that the function is indirectly part of > a COALESCE input set and so it goes ahead and performs its optimization but > pre-executing the function and caching its results. Declaring the function IMMUTABLE asserts that there are no user-visible side effects, so the database engine is free to rearrange the code. At that point, one can hardly complain about a rearrangement that causes the side effects to happen at unexpected times. Neither is any particular optimization affecting when the side effects appear *required*. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
