"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:11105.1040238975@sss.pgh.pa.us...
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > I expected that if a function is cachable and is used
> > inside a select like:
> > SELECT * FROM foo WHERE id = my_func( a_param )
> > then the function my_func is called one for each different value
> > of a_param.
>
> That is not what "cachable" means to Postgres. There is no function
> result cache such as you seem to envision.
>
> regards, tom lane
Just to understand, do you mean that the following behaviour is
correct?
# create table test( a int, b int );
CREATE
# insert into test values (1,2);
INSERT 79298997 1
# insert into test values (1,2);
INSERT 79298998 1
# insert into test values (1,2);
INSERT 79298999 1
#select * from test where a = sp_cache( 3 );
NOTICE: CALLED
a | b
---+---
(0 rows)
#select sp_test ( 4);
NOTICE: CALLED
NOTICE: CALLED
NOTICE: CALLED
sp_test
---------
(1 row)
where sp_cache is a function declared ( iscachable )
and do a notice each time that is called
and sp_test is a function that perform:
"select * from test where a = sp_cache( 3 )"
How you can see in the first case I see only a NOTICE
in the second case I see 3 NOTICE, why ? Am I wrong ?
Ciao
Gaetano