From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'SQL Postgresql List' <pgsql-sql@postgresql.org>
Subject: Function Volatility
Date: Sun, 9 Sep 2007 21:24:30 -0300
Message-ID: <017901c7f340$f497e400$8f01010a@iptel.com.ar> (raw)
Hi guys,
I am not sure if I am understanding volatility.
My issue is better explained with a quick example. The function below
expresses call durations in minutes and it is immutable.
CREATE OR REPLACE FUNCTION dur2min(secs INTEGER) RETURNS INTEGER
AS $$
BEGIN
RAISE NOTICE 'BEEN HERE!';
RETURN CEIL(secs/60.0);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
# SELECT dur2min(30) as c1, dur2min(30) as c2, dur2min(30) as c3;
NOTICE: BEEN HERE!
NOTICE: BEEN HERE!
NOTICE: BEEN HERE!
c1 | c2 | c3
----+----+----
1 | 1 | 1
(1 row)
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?
I was under the impression that under these conditions it could *reuse* the
result of the first call. The manual states the planner should avoid
reevaluate the function but I'm not sure what that means as it *is*
executing it every time.
My goal of course is that the function gets executed only once per row.
I'm using 8.2.4
Thanks for your hindsight.
Regards,
Fernando.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar
Subject: Re: Function Volatility
In-Reply-To: <017901c7f340$f497e400$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox