Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3C8BD9F978C for ; Sun, 9 Sep 2007 21:24:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 54085-08 for ; Sun, 9 Sep 2007 21:23:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id 4EC669F9787 for ; Sun, 9 Sep 2007 21:23:52 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953 for ; Sun, 09 Sep 2007 21:23:50 -0300 From: "Fernando Hevia" To: "'SQL Postgresql List'" Subject: Function Volatility Date: Sun, 9 Sep 2007 21:24:30 -0300 Organization: IP-TEL Message-ID: <017901c7f340$f497e400$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_017A_01C7F327.CF4AAC00" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcfzQPRctMPcoZ+RSmmSiPXZX+9XBg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/69 X-Sequence-Number: 29282 This is a multi-part message in MIME format. ------=_NextPart_000_017A_01C7F327.CF4AAC00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_017A_01C7F327.CF4AAC00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Function Volatility

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.

------=_NextPart_000_017A_01C7F327.CF4AAC00--