Sam Mason <sam@samason.me.uk> writes:
> On Thu, Jun 04, 2009 at 09:48:17AM -0400, Tom Lane wrote:
>> Actually, if you're intending that a SQL function be inline-able then
>> it's best *not* to mark it as IMMUTABLE (nor STRICT either). If the
>> marking matches the behavior of the contained expression then it
>> doesn't matter, but if the marking is stricter than the expression
>> it will prevent inlining.
> Why would strictness alter planning?
Because it alters behavior. For example, consider
create function foo(int) returns int as
$$ select coalesce($1, 42); $$ language sql strict;
Because this function is declared STRICT, it must return null on null
input. However, the contained expression would not act that way ---
it would in fact return 42 for null input. Therefore inlining would
change the behavior, and therefore we don't inline.
The converse case (function not marked strict, but expression really
is) doesn't pose a hazard. So it's easier to just not mark the function
strict than to think carefully about whether (a) the contained
expression is strict and (b) the planner will be able to prove that.
The same sorts of considerations arise for marking the function as
less volatile than the contained expression really is. In this case
the "behavioral change" has to do with what later phases of the planner
will think they can do with the function or expression. The bottom line
is the same though: easier to leave off the marking.
regards, tom lane