10

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dorsey
  • 854
  • 3
  • 9
  • 17
  • 2
    Note that quoting the language's name is deprecated and will give you an error in 9.2 you should use `LANGUAGE plpgsql` instead. –  Sep 25 '12 at 06:09

2 Answers2

27

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
  • 605,456
  • 145
  • 1,078
  • 1,228
Akash KC
  • 16,057
  • 6
  • 39
  • 59
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
  • 605,456
  • 145
  • 1,078
  • 1,228