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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
