Function Volatility

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'SQL Postgresql List'" <pgsql-sql(at)postgresql(dot)org>
Subject: Function Volatility
Date: 2007-09-10 00:24:30
Message-ID: 017901c7f340$f497e400$8f01010a@iptel.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message John Summerfield 2007-09-10 00:52:25 postgresql HEAD build failure
Previous Message Filip Rembiałkowski 2007-09-09 11:01:52 Re: work hour calculations