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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
