Thread: how to evaluate a function only once for a query?

Hello!

I'm tring to use postgesql functions written in pgtcl.
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. The problem is that
it will return each time the same value (because the argument is
constant) and takes a lot of time to execute because my function is slow.
The ideal behaviour  would be that the function is called only once when
the query is interpreted and then the value is used as a constant.
Is there any possibility to make it behave like that?
I know that I can make a "select timevalue('something')" before and then
invoke my query with the result, but I'd like to have only one query for
this.

Thanks,
Nicolae Mihalache

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

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