SQL VOLATILE

What does the VOLATILE keyword do in PostgreSQL?

Marks a PostgreSQL function as non-deterministic, telling the planner that it may return different results on each call and should not be optimized away.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VOLATILE: PostgreSQL: Yes MySQL: No SQL Server: No Oracle: No SQLite: No Teradata: Has VOLATILE TABLE (different concept)

SQL VOLATILE Full Explanation

In PostgreSQL, VOLATILE is one of three volatility classifications (VOLATILE, STABLE, IMMUTABLE) that can be assigned to a user-defined function or procedure. A VOLATILE function can read or modify database state, depend on session settings, call random() or clock_timestamp(), or otherwise produce different output for the same input. Because its result is unpredictable, PostgreSQL executes the function every time it appears in a query, forbids constant folding, and prevents its use in index expressions or materialized view definitions.VOLATILE is the default when no classification is given, but explicitly declaring it communicates intent and helps future maintainers. Misclassifying a function can hurt performance or, worse, return stale or incorrect results. Functions that only read data without side effects should be STABLE, while completely deterministic functions should be IMMUTABLE.

SQL VOLATILE Syntax

CREATE [ OR REPLACE ] FUNCTION function_name ( [ parameters ] )
RETURNS return_type
VOLATILE
LANGUAGE plpgsql
AS $$
  -- function body
$$;

-- Change volatility
ALTER FUNCTION function_name ( [ arg_types ] ) VOLATILE;

SQL VOLATILE Parameters

Example Queries Using SQL VOLATILE

-- Example 1: A non-deterministic function that returns a random integer
CREATE OR REPLACE FUNCTION public.rand_int(max_val int)
RETURNS int
VOLATILE
LANGUAGE sql
AS $$
  SELECT floor(random() * max_val)::int;
$$;

-- Example 2: Marking an existing function VOLATILE
ALTER FUNCTION public.get_server_pid() VOLATILE;

Expected Output Using SQL VOLATILE

The functions are created or altered successfully
PostgreSQL will now treat each call as potentially different and will not cache or inline the results

Use Cases with SQL VOLATILE

Wrapping random(), clock_timestamp(), or nextval()
Functions that INSERT, UPDATE, or DELETE rows
Functions that write to logs or raise notices
Session-dependent logic (current_setting, temp tables)
Any function whose result can change between calls within a single statement

Common Mistakes with SQL VOLATILE

Assuming VOLATILE is always the best choice; use STABLE or IMMUTABLE when possible
Using VOLATILE functions in index expressions or partition keys (PostgreSQL blocks this)
Expecting the planner to cache results of a VOLATILE function
Forgetting that VOLATILE is already the default and double-declaring it in every function

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

Is VOLATILE the default in PostgreSQL?

Yes. If you omit any volatility clause, PostgreSQL marks the function VOLATILE automatically.

Can a VOLATILE function appear in an index?

No. PostgreSQL blocks VOLATILE functions from index expressions or materialized views because their output can change between scans.

Does VOLATILE affect performance?

Yes. A VOLATILE function is executed for every row where it appears, skipping constant folding and some plan optimizations. Use STABLE or IMMUTABLE when safe.

How do I change a function to VOLATILE?

Run ALTER FUNCTION function_name(arg_types) VOLATILE; PostgreSQL updates the catalog immediately, and future queries honor the new classification.