Function Volatility

<p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"><font face="Arial" size="2">Hi guys,</font></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">I am not sure if I am understanding
volatility.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">My</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">issue</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">is better explained with a quick example.</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"> <font face="Arial" size="2">The function</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"> <font face="Arial" size="2">below expresses call
durationsin minutes and it</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">is immutable.</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">CREATE OR
REPLACEFUNCTION dur2min(</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2">secs</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> INTEGER) RETURNS INTEGER</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">AS$$</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">BEGIN</font></span><p
dir="LTR"><spanlang="en-us">        <font face="Arial" size="2">RAISE NOTICE 'BEEN HERE!';</font></span><p
dir="LTR"><spanlang="en-us">        <font face="Arial" size="2">RETURN CEIL(</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">secs</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">/60.0);</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">END;</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">$$ LANGUAGE 'plpgsql' IMMUTABLE;</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"># SELECT
dur2min(30)as c1, dur2min(30) as c2, dur2min(30) as c3;</font></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">NOTICE:  BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">NOTICE:  BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">NOTICE: BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2"> c1 | c2 |
c3</font></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"><font face="Arial"
size="2">----+----+----</font></span><pdir="LTR"><span lang="es-ar"><font face="Arial" size="2">  1 |  1 | 
1</font></span><pdir="LTR"><span lang="es-ar"><font face="Arial" size="2">(1 row)</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">Wh</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">at</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">bother</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> me</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">are</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> the 3</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">“</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">been here</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">”</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> messages. As the function is immutable and the
parameterremains unchanged</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">needs</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> the planner</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> actually execute the function 3 times?</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">I was under the impression that</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> under these conditions</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"> it could
*</font></span><spanlang="es-ar"><b></b></span><span lang="es-ar"><b></b></span><b><span lang="en-us"><font
face="Arial"size="2">reuse</font></span></b><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">* the result of the</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> first call</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">. The
manual</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">states</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">theplanner</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">should</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> avoid</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> reevaluate the function but I</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">’</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">m not sure what that means as it *</font></span><span
lang="es-ar"><b></b></span><spanlang="es-ar"><b></b></span><b><span lang="en-us"><font face="Arial"
size="2">is</font></span></b><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">*executing it every time.</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">My goal of course is that the function get</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">s</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"> executed only once per
row.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">I</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">’</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">m using 8.2.4</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">Thanks for
yourhindsight.</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">Regards,</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">Fernando.</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span>