Re: Does it make sense to break a large query into separate functions?

On Wed, May 8, 2013 at 4:47 PM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> On 09/05/13 09:35, Merlin Moncure wrote:
> [...]
>
>
>> More oddness -- when I wrap, say, random() with stable function, I get
>> unique value per returned row, but same value across the set when wrapped
>> with immutable.
>
> [..]
>
> That part I think I know (but, I'm often wrong!).
>
> By saying it is immutable, you are saying that the values returned for the
> same set of parameters is always the same. The system looks at your
> immutable function that wraps random() and 'knows' that once invoked, the
> value returned will always be the same, so why would it want to invoke your
> immutable function multiple times, as it can just do that once and reuse the
> value returned?

right. note I think that (reduced invocation of immutable function) is
the correct behavior. If I say something is immutable, rightly or
wrongly, I am giving postgres a free hand to reduce function calls.
But (wandering off topic here), But I'm wondering why the stable
wrapper doesn't exhibit the same behavior. In other words I'm
pedantically chasing down the in-query planning differences between
immutable and stable -- I don't think there should be any (but I
certainly could be wrong about that).

*) shouldn't a stable and immutable functions have the same semantics
from query planning type of view? To me, immutable conveys all the
guarantees of 'stable', plus the additional guarantee of function
stability beyond the current transaction.

merlin