15 ms·

IMMUTABLE STRICT is used properly in all of these examples. However, it is important to not lie to postgres and declare your functions are IMMUTABLE (always sam

• jpitz 13y ago
  Devil's Advocate: It is important to get them _right_, because postgres will cache the tar out of a IMMUTABLE function result, and <mumbling>something something optimize STRICT calls, maybe?</mumbling>
  • fdr 13y ago
    An immutable mark will allow the optimizer to constant-fold an expression that contains only IMMUTABLE operations. It basically has to believe you on this one, and being wrong is not good at all. STRICT is less advisory: if specified, the function will not be evaluated if it any NULL is seen in the argument list (NULL will be yielded immediately), without ever passing control to the code in the function.
    • ithkuil 13y ago
      well, beside optimization, a function marked immutable is important because indexing on function return values makes only sense if the function is idempotent.
      • victorNicollet 13y ago
        Idempotent is probably not what you meant. It makes sense to index on, say, `reverse(name)` (to perform suffix searches), and that function is immutable and deterministic, but it is not idempotent (except on palindromes).
        • ithkuil 13y ago
          wow, there seems to be a degree of confusion about this term, depending on the context. Mathematically speaking it means that you can apply it multiple times and get the same result, i.e. reverse(reverse(name)) == reverse(name)" However, I heard it using it many times where the correct term would be "pure function" (one whose output is defined solely by its input). But this is clearly misleading. I guess that the confusion stems from the use of "applying multiple times". Formally multiple function application means f(f(x)), and not simply repeating function evaluation f(x); f(x). Furthermore I saw people distinguishing "side effect idempotence" (observed by all pure functions) and "return value idempotence". To add more confusion to that, some some HTTP (GET/...) operations are called idempotent, even though it's clearly not in functional sense (if you take the output of the GET and feed it as argument to another GET, you won't get the same result again), but only meaning "side effect free".
• Someone 13y ago
  IMMUTABLE also implies "does not change the database". http://www.postgresql.org/docs/9.2/static/sql-createfunction.html http://www.postgresql.org/docs/9.2/static/sql-createfunction...: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. STRICT does not mean 'no side effects'; it means 'returns NULL when given a NULL argument'. From the same page: CALLED ON NULL INPUT (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately. RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically. [looked that up because I couldn't think of a function that was STRICT but not IMMUTABLE according to the definition given]