how to evaluate a function only once for a query?

On Thursday 20 Jun 2002 11:40 am, Nicolae Mihalache wrote:

However, when I do a query like:
"select * from mytable where time<timevalue('something')" I see that my
function is evaluated once for each row in mytable. The problem is that
it will return each time the same value (because the argument is
constant)

Try marking the function "iscachable" when you define it - it's designed for
exactly this situation and says that for any fixed argument your function
will return a fixed result.. I don't use pltcl but I believe that works
regardless of the function language. See the manuals for CREATE FUNCTION for
further details.

- Richard Huxton

Nicolae Mihalache <mache@abcpages.com> writes:

I've created a function timevalue(text) that returns a timestamp as
result. The function works just fine.
However, when I do a query like:
"select * from mytable where time<timevalue('something')" I see that my
function is evaluated once for each row in mytable.

See the "isCachable" attribute in CREATE FUNCTION.

regards, tom lane

Having previously on this list displayed my ignorance
of the true nature of "iscachable", I had thought that
I had it straight. The response below has brought
back this bad memory, and now prompts me to check
again:
The only way that "iscachable" would prevent the need
for reevaluation on every row is by permitting the
creation of an index on the function return values
(which step I assume is implicit in the response
below).
Right?

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nicolae Mihalache <mache@abcpages.com> writes:

I've created a function timevalue(text) that

returns a timestamp as

result. The function works just fine.
However, when I do a query like:
"select * from mytable where

time<timevalue('something')" I see that my

function is evaluated once for each row in

mytable.

See the "isCachable" attribute in CREATE FUNCTION.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

Tom Lane wrote:

Nicolae Mihalache <mache@abcpages.com> writes:

I've created a function timevalue(text) that returns a timestamp as
result. The function works just fine.
However, when I do a query like:
"select * from mytable where time<timevalue('something')" I see that my
function is evaluated once for each row in mytable.

See the "isCachable" attribute in CREATE FUNCTION.

regards, tom lane

thanks for your quick answer.
I'm tring to use this attribute but I get an error:
robust=# create function testfunc () returns text as '' language 'pltcl'
with iscachable;
ERROR: parser: parse error at or near "iscachable"

any ideea? Is it possible that my postgres 7.2.1 does not support this
attrributes? I also get error if I'm tring to use the other attribute
"isstrict"

Thanks,
Nicolae Mihalache

Nicolae Mihalache <mache@abcpages.com> writes:

robust=# create function testfunc () returns text as '' language 'pltcl'
with iscachable;
ERROR: parser: parse error at or near "iscachable"

I think it's
with (iscachable);
with (iscachable, isstrict);
etc

regards, tom lane

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:

The only way that "iscachable" would prevent the need
for reevaluation on every row is by permitting the
creation of an index on the function return values

As far as I could see, he wanted the system to pre-evaluate a call
of the function with a literal-constant argument --- which is exactly
what isCachable is all about.

You're correct that in a case like

select ... where myfunc(field1) = 'constant';

the only thing that will help is an index on myfunc(field1). But this
case is quite different from

select ... where field1 = myfunc('constant');

BTW, for 7.3 the name "isCachable" will be deprecated; we now recommend
"immutable" for the same concept. Hopefully this will serve to avoid
some confusion. See the development docs for CREATE FUNCTION at

http://developer.postgresql.org/docs/postgres/sql-createfunction.html

regards, tom lane