Ivan Sergio Borgonovo wrote:
> http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html
>
> "A STABLE function cannot modify the database and is guaranteed to
> return the same results given the same arguments for all rows within
> a single statement. This category allows the optimizer to optimize
> multiple calls of the function to a single call. In particular, it is
> safe to use an expression containing such a function in an index scan
> condition. (Since an index scan will evaluate the comparison value
> only once, not once at each row, it is not valid to use a VOLATILE
> function in an index scan condition.)"
>
> I can't understand how it can call a function a single time and avoid
> to cache the result.
> Is it limited to a single statement?
You did not notice the sentence *before* that list:
"The volatility category is a promise to the optimizer about
the behavior of the function"
What is meant is this:
You must not define a function STABLE unless you are certain that it
will always have the same result if called multiple times with the
same arguments within a single query.
The function itself will not behave any differently if you define
it IMMUTABLE or if you define it VOLATILE. It's up to the function definer
to choose the right setting.
If you do it wrong, weird things may happen.
As an example, if you define:
CREATE FUNCTION random_nr() RETURNS double precision
IMMUTABLE LANGUAGE sql AS 'SELECT random()';
and you try to select 10 random numbers like:
SELECT random_nr() FROM generate_series(1, 10);
you will get the same number 10 times, because the optimizer will
cause the function to be called only once. If you define the function
as VOLATILE, it will be called ten times because the optimizer knows
that it cannot reuse the first result again.
On the other hand, if you
CREATE OR REPLACE FUNCTION to_upcase(text) RETURNS text
VOLATILE STRICT LANGUAGE plpgsql AS
'BEGIN RETURN upper($1); END;';
and you define
CREATE TABLE tab (id integer PRIMARY KEY, val text UNIQUE);
INSERT INTO tab VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
then look at the following EXPLAIN output:
EXPLAIN SELECT id FROM tab WHERE val = to_upcase('three');
QUERY PLAN
-----------------------------------------------------
Seq Scan on tab (cost=0.00..332.88 rows=1 width=4)
Filter: (val = to_upcase('three'::text))
(2 rows)
If you had defined the function as IMMUTABLE, the optimizer would know
that it is safe to use the function in an index scan, because it won't
change value:
EXPLAIN SELECT id FROM tab WHERE val = to_upcase('three');
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using tab_val_key on tab (cost=0.00..8.27 rows=1 width=4)
Index Cond: (val = 'THREE'::text)
(2 rows)
Yours,
Laurenz Albe
