Re: ideally a non-volatile function?

Noel Whelan wrote:
> 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.
If your function is written in plpgsql, you it should be always safe to
mark it STABLE (instead of VOLATLE), if the following conditions are met
.) Your function doesn't use functions marked VOLATILE
.) You don't use "select ... limit x" without also including an
"order by" clause.

VOLATILE means that the return value of the function is in a way
"unpredictable" - the function may return different return values, even
if it is passed the same arguments, and sees the same data in the database.

> 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).
STABLE (as opposed to VOLATILE) only means that your function returns
the same result when passed the same parameters _and_ the database has
not changed (or the changes are not visible to you), as far as I know.
You should therefore be able to mark your function STABLE.

greetings, Florian Pflug