Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does “LANGUAGE 'plpgsql' VOLATILE” mean?

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?

like image 669
Dorsey Avatar asked Sep 25 '12 06:09

Dorsey


People also ask

What is Plpgsql language?

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998.

What is a VOLATILE default Postgres?

VOLATILE is the default if the CREATE FUNCTION command does not specify a category. The volatility category is a promise to the optimizer about the behavior of the function: A VOLATILE function can do anything, including modifying the database.

What is Plpgsql function?

With PL/pgSQL you can group a block of computation and a series of SQL queries inside the database server, thus having the power of a procedural language and the ease of use of SQL. Also, with PL/pgSQL you can use all the data types, operators and functions of Greenplum Database SQL.

What is VOLATILE parallel unsafe?

A parallel unsafe operation is one that cannot be performed while parallel query is in use, not even in the leader.


1 Answers

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().

like image 70
Akash KC Avatar answered Oct 01 '22 04:10

Akash KC