> -----Original Message-----
> There really needs to be some way to specify that when an expression is
> evaluated for each row in a set, a function used within that expression is
not
> optimized away for some rows. Fortunately we have a way:
>
> http://www.postgresql.org/docs/9.2/interactive/sql-createfunction.html
>
> | VOLATILE indicates that the function value can change even within a
> | single table scan, so no optimizations can be made. Relatively few
> | database functions are volatile in this sense; some examples are
> | random(), [...]
>
> The behavior in the OP's query would certainly be sane if the function
were
> not VOLATILE; as it is, I have a hard time seeing this as anything but a
bug.
What are the arguments against adding a 4th identifier - call it PER_ROW for
this argument? The main reason VOLATILE is broken is that it is the default
and in order to minimize beginner's penalty it is not treated as such in
some situations. The new one could behave just like VOLATILE but would
never be optimized away and would always evaluate once for each row in its
context.
Then the question is whether you write a new "random()" function or break
backwards compatibility and alter the existing version.
David J.