On: Sunday, July 13, 2003 4:19 AM "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Mendola Gaetano" <mendola@bigfoot.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