Thread: IMMUTABLE bug ?

I cant get the IMMUTABLE modifier meaning.

The 'testme' IMMUTABLE function is invoked 3 times 
in the following query:
 # select testme(1), testme(1), testme(1); NOTICE:  called NOTICE:  called NOTICE:  called  testme | testme | testme
--------+--------+-------- ret    | ret    | ret (1 row)
 

Why is so ? shouldn't the IMMUTABLE keywork make
it a single call ?


From the 8.0 manual :
 IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it
doesnot do database lookups or otherwise use information not directly present in its argument list. If this option is
given,any call of the function with all-constant arguments can be immediately replaced with the function value.
 

The function definition:

 CREATE OR REPLACE FUNCTION testme(integer) RETURNS text AS ' BEGIN         RAISE NOTICE ''called'';         return
''ret''::text;END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT;
 


PostgreSQL version 8.0.0



--strk;

strk <strk@keybit.net> writes:
> Why is so ? shouldn't the IMMUTABLE keywork make
> it a single call ?

No.  There is no function value cache.  What does happen here is that
the planner folds those calls to constants at plan time, instead of at
run time.  Try
select testme(1) from some-table-with-multiple-rows

and note there's only one call not N.
        regards, tom lane