Re: Function immutable is not during a reindex ?

On: Sunday, July 13, 2003 4:19 AM "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Mendola Gaetano" <mendola(at)bigfoot(dot)com> writes:
> > the function is immutable but is executed 3 times
> > ( one for each row).
>
> So? Sounds to me like it's working as intended.

Well the documentation says:
IMMUTABLE [...] If this option is given,
any call of the function with all-constant
arguments can be immediately replaced
with the function value.

The "index" behaviuor is different if the same function is used
for a default value, or as field in a select:

if I look at the table of the example:

#\d t_a
Table "public.t_a"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
Indexes: idxv btree (b) WHERE (test(3) = b)

I was expecting:
Indexes: idxv btree (b) WHERE (4 = b)

look now the differrent behaviour:

Used as field in a select:

#select *, test(2) from t_a;
NOTICE: test called
a | b | test
---+---+------
1 | 0 | 3
1 | 1 | 3
1 | 2 | 3
(3 rows)

Used as default value:
# alter table t_a alter b set default test(3);
NOTICE: test called
ALTER TABLE

#\d t_a
Table "public.t_a"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer | default 4
Indexes: idxv btree (b) WHERE (test(3) = b)

look that in the case of default value there is 4 and in
case of index there is still the call.

I don't like neather the result of the following experiment:
# select *, test(a) from t_a;
NOTICE: test called
NOTICE: test called
NOTICE: test called
a | b | test
---+---+------
1 | 0 | 2
1 | 1 | 2
1 | 2 | 2
(3 rows)

here is called 3 times with the same argumen '1', I'm not sure
but with the previous version of postgres 7.2.X or 7.1.X
( when there only way was write: WITH ( iscachable ) )

that select
was like this:
# select *, test(a) from t_a;
NOTICE: test called
a | b | test
---+---+------
1 | 0 | 2
1 | 1 | 2
1 | 2 | 2
(3 rows)

and test(1) was correctly cached, I'm not sure about this but you see
the difference when is used inside a default value and inside an index ?

Regards
Gaetano Mendola