In PostgreSQL, every function has one of the possible volatility classifications: volatile, static and immutable. When it is not specified, PostgreSQL assumes that it is a volatile function, which is not always the case.
Volatile: functions with side-effects.
Immutable: pure functional functions.
Stable: smth in between. (wow, such explanation, very clear *applause*)
Yes, IMHO stable is utterly error-prone and you need to consult to the documentation. Sorry that TL;DR was inadequate 😦 ..(but.. it is supposed to be inadequate by definition right?? whatever..)
So, I’d like to draw your attention to immutable functions before this post gets too long. When declaring a function, using the immutable keyword actually implies the following:
- I solemnly swear that my function does not modify the db.
- It does not do any database lookups.
- Given the same arguments, it ALWAYS returns the same output.
BEWARE: As stated in the PostgreSQL documentation, your function should be volatile if you use anything like random(), currval(), timeofday(), current_timestamp(), etc. in your function.
Reader: “So, like.. when shall I use immutable functions? “
Recently, we had to implement a hash function and it was much more appropriate to do the job in PL / pgSQL. So it was all sunshine and lollipops, the hash function came out well etc. But unfortunately it was a tad bit slow. To fix this issue, we just altered the volatility classification of some helper functions. For example, a function that converts characters into ascii values was perfectly suitable to use the immutable keyword!