Обсуждение: 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