SQL STABLE

What does STABLE mean in SQL functions?

Marks a user-defined function as stable, guaranteeing no data modification and a consistent result within a single statement execution.

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 STABLE: PostgreSQL, Amazon Redshift, YugabyteDB (Postgres compatible). Not supported in MySQL, SQL Server, Oracle, or SQLite.

SQL STABLE Full Explanation

STABLE is a volatility classification used in PostgreSQL (and derivatives such as Amazon Redshift) when defining or altering a user-defined SQL, PL/pgSQL, or C function. It tells the query planner that the function reads data but does not write it, and that its return value is constant for all calls made inside the same SQL statement. Because the result cannot change during a statement, the planner may evaluate the function once per row or even once per scan, and may cache the output for optimization. Unlike IMMUTABLE, a STABLE function may depend on table contents, configuration parameters, or the current transaction snapshot. Unlike VOLATILE, it cannot contain side effects such as INSERT, UPDATE, DELETE, or calling other volatile functions. Misclassifying volatility can lead to wrong results or unnecessary re-execution, so choosing STABLE when appropriate improves performance without sacrificing correctness. The attribute is applied in CREATE FUNCTION or ALTER FUNCTION and remains until explicitly changed.

SQL STABLE Syntax

CREATE OR REPLACE FUNCTION function_name(arg_list)
RETURNS return_type
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    -- function body
END;
$$;

SQL STABLE Parameters

Example Queries Using SQL STABLE

-- 1. Create a stable function that looks up a user's full name
CREATE OR REPLACE FUNCTION get_full_name(p_user_id INT)
RETURNS TEXT
LANGUAGE SQL
STABLE
AS $$
    SELECT first_name || ' ' || last_name
    FROM users
    WHERE id = p_user_id;
$$;

-- 2. Use inside another query; the planner may call it once per row
SELECT order_id,
       get_full_name(customer_id) AS customer
FROM   orders
WHERE  order_date >= CURRENT_DATE - INTERVAL '30 days';

-- 3. Change volatility later
ALTER FUNCTION get_full_name(INT) VOLATILE;

Expected Output Using SQL STABLE

Function is created and stored with STABLE volatility.
When the SELECT runs, PostgreSQL evaluates get_full_name for each referenced row but may cache results within the statement for speed.
The ALTER FUNCTION command updates the function's volatility to VOLATILE.

Use Cases with SQL STABLE

Functions that query data but do not modify it (look-ups, aggregations, formatting)
Functions that must see a consistent snapshot within a statement but may change between transactions
Improving planner optimization and result caching without marking the function IMMUTABLE

Common Mistakes with SQL STABLE

Marking a function STABLE when it performs writes or calls volatile functions, causing silent data corruption
Assuming STABLE means the result is cached across separate statements or transactions
Forgetting to reclassify a function after adding side effects

First Introduced In

PostgreSQL 7.4

Frequently Asked Questions

What happens if I mislabel a volatile function as STABLE?

PostgreSQL may cache its result within a query, leading to inconsistent data or missed side effects. Always classify based on the most volatile operation inside the function.

Can a STABLE function read from tables?

Yes. It can run SELECT statements and depend on the current transaction snapshot. It just cannot modify data.

Is STABLE required for performance?

Not mandatory, but using it correctly lets the planner avoid redundant executions and can noticeably speed up queries that call the function many times.

How do I change a function from VOLATILE to STABLE?

Use ALTER FUNCTION function_name(arg_types) STABLE; The change takes effect immediately for new sessions.