Re: Immutable function with bind value

On Jan 20, 2012, at 6:15, Brice Maron <bmaron(at)gmail(dot)com> wrote:

> Hi,
>
> i've discovered something kind of weird while developing my app...
> I was trying to fetch some records in a table using a function immutable.
> In my interface it was really slow and while i was in a psql it was
> really fast ...
>
> After some research i've found out that it was caused by the bind
> parameter "forcing" the immutable function to execute each time my
> query gets a record.
>
> while i know that the value can't be known at planning time the
> difference between the binded / not binded is quite enormous...
>
> i've isolated a test case here....
>
> https://gist.github.com/e93792540cb3a68054c9
>
> (it happens at least in pg 8.4 and 9.1.2)
>
>
> What do you think about it... bug? feature? how can i avoid it?
>
>
> Thanks
>
>

While you prove the behavior exists your test case is not a realistic example of why you would do such a thing.

I would have to say that I'd expect your query to execute the function once and cache the result for the remainder of the statement. To that end have you tried defining it as a STABLE function instead of immutable?

In the real use-case what is it you are trying to accomplish?

You might try using a WITH clause to resolve your function call and then use the result in the main query.

David J.