PostgreSQL: Immutable, Volatile, Stable

IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc.

STABLE can use any inputs that are themselves STABLE: other STABLE or IMMUTABLE functions, and SELECT queries of tables. It's safe to query tables because the function's view of those tables will not change within the query's current snapshot. You can access GUC values (current_setting(...)) so long as you know they won't also be assigned to within the current statement.

VOLATILE functions are everything that doesn't fit the above:

• Anything with side-effects
• Anything that does writes
• Anything that queries external data not managed by the PostgreSQL snapshot
• ...

In general, just leave everything VOLATILE unless you have a good reason not to.

The main reason to use IMMUTABLE is when writing functions that are to be used as part of index expressions.

For STABLE, the part you need to bold is 'result could change across SQL statements'

IMMUTABLE things are not supposed to change ever. Even if you restart your database server, run yum update (but of course there can be bugs!), change your configuration (like datestyle, timezone, default_text_search_config, extra_float_digits, etc.), or replace your server hardware entirely (of the same architecture as the old hardware, so the binary files are still compatible).

The functions you describe sound like they are STABLE, because within a single SQL statement they will execute their queries using the same snapshot as the outer query has, and so any concurrent changes you made to those other tables would not be visible. Now, if your functions opened a new connection to the server and run their queries within that independent connection, that would make the function volatile, because they would be using different snapshots.