The information on what 'volatile' indicates with a function includes the
following:
'VOLATILE indicates that the function value can change even within a single
table scan, so no optimizations can be made. Relatively few database
functions are volatile in this sense; some examples are random(), currval(),
timeofday().'
I've got a function that I would like to be optimized if it could be; but
which currently is identified as volatile (I never indicated otherwise when
creating it). I would just like to be clearer on what the effects could be
of changing this before I do.
The function I've got is essentially based on a view which gives back info
from a variety of tables on an item with an ID of 1. I created the view
itself only in order to give a 'format' to the value I get back from this
function, in which I create a variable that's given that view as a type. The
other variable is an integer which is the ID of the item I would like
information on (instead of '1').
I'm not certain, firstly, whether the 'function value' in this case would be
the integer passed to the function, or the value it gives back. If the
former, it will not change within the function; but it could if it's the
latter of these, because in the query I get a few pieces of info with an
'offset 0 limit 1' in the where clause in order to identify only the latest
entry (based on an order by date/time). If the item with the ID passed to
the function were being edited while the function itself was executing, this
value could change; but I can imagine no other ill effects (it would just
give back a value that was incorrect; but very infrequently and therefore a
non-issue, imho).
In this case, would it be better to create the function as non-volatile? I'm
thinking it would be; but just would like to confirm I wouldn't be causing
any issues by doing it..
Thanks for any input.