To: "Fernando Hevia" <fhevia@ip-tel.com.ar>
cc: "'SQL Postgresql List'" <pgsql-sql@postgresql.org>
Subject: Re: Function Volatility 
In-reply-to: <017901c7f340$f497e400$8f01010a@iptel.com.ar> 
References: <017901c7f340$f497e400$8f01010a@iptel.com.ar>
Comments: In-reply-to "Fernando Hevia" <fhevia@ip-tel.com.ar>
	message dated "Sun, 09 Sep 2007 21:24:30 -0300"
Date: Sun, 09 Sep 2007 22:15:24 -0400
Message-ID: <15490.1189390524@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> I am not sure if I am understanding volatility.

You're not.

> What bother me are the 3 "been here" messages. As the function is immutable
> and the parameter remains unchanged needs the planner actually execute the
> function 3 times?

The IMMUTABLE marker is a promise from you to the system that it is safe
to optimize away multiple calls to the function.  It is not a promise
from the system to you that the system will expend unlimited amounts of
energy to detect duplicate calls.  The majority of the immutable
functions in Postgres are things like int4pl(), where it would obviously
be silly to expend any cycles at all on looking for duplicate calls such
as you show here.

In practice what will happen is that each textual call will be folded to
a constant separately.  The advantage comes from not having to repeat
the call for each row processed by a query, not from saving work within
a row.

			regards, tom lane