When I create or update a function or procedure in a Postgres database I see LANGUAGE 'plpgsql' VOLATILE at the end of function.
What does this mean and what is its purpose?
Asked
Active
Viewed 1.5k times
10
Erwin Brandstetter
- 539,169
- 125
- 977
- 1,137
Dorsey
- 844
- 2
- 9
- 16
-
2Note that quoting the language's name is deprecated and will give you an error in 9.2 you should use `LANGUAGE plpgsql` instead. – a_horse_with_no_name Sep 25 '12 at 06:09
-
1Why not just read the documentation? – ertx Sep 25 '12 at 08:48
2 Answers
24
From Postgres docs:
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(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().
Erwin Brandstetter
- 539,169
- 125
- 977
- 1,137
Akash KC
- 15,511
- 5
- 36
- 58
9
Not least, LANGUAGE 'plpgsql' VOLATILE means that somebody didn't get the memo.
The language name in CREATE FUNCTION is an identifier and should not be quoted. Should be:
LANGUAGE plpgsql VOLATILE
Malpractice can lead to confusing errors. See:
About function volatility:
Erwin Brandstetter
- 539,169
- 125
- 977
- 1,137