IMMUTABLE and STABLE in SQL

Volatility is a promise to the planner about what repeat calls may return.

-- Volatility is a promise to the planner about repeat calls.
CREATE FUNCTION card_fee(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE            -- same input, same answer, forever
RETURN round(amount * 0.029 + 0.30, 2);

CREATE FUNCTION todays_orders()
RETURNS BIGINT
LANGUAGE SQL
STABLE               -- steady within one statement, not across time
BEGIN ATOMIC
    SELECT COUNT(*)
    FROM orders
    WHERE placed_at > now() - INTERVAL '1 day';
END;

SELECT card_fee(413.45) AS fee, todays_orders() AS today;

How it works

1. IMMUTABLE swears the fee for an amount never changes, so results can be reused.
2. STABLE promises steadiness within one statement — right for reads of table data.
3. Mislabeling is a real bug: the planner will cache what you told it it could.

Keywords and builtins used here

• AS
• ATOMIC
• BEGIN
• BIGINT
• COUNT
• CREATE
• END
• FROM
• FUNCTION
• IMMUTABLE
• INTERVAL
• LANGUAGE
• NUMERIC
• RETURN
• RETURNS
• SELECT
• SQL
• STABLE
• WHERE

The run, in numbers

Lines

18

Characters to type

507

Tokens

78

Three-star pace

65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 72 seconds.

Type this snippet

Step 4 of 4 in Functions, step 4 of 23 in PostgreSQL Stored Procedures.

← Previous Next →