Thread: Evaluate arguments of COALESCE and NULLIF only once.

Currently the parser transforms COALESCE and NULLIF statements to an
equivalent CASE statement.  The downside to this is that arguments must be
evaluated for both the WHEN and THEN sections of the CASE statement.
This is a problem if the arguments are expensive or volatile functions.
Consider evaluating

SELECT COALESCE(ten_millionth_digit_of_pi(),1);

OR

SELECT COALESCE(randomlynull(), 1);

The first statement will take twice as long.  The second statement will
sometimes return NULL because the first evaluation returns a non null
value, but the second evaluation returns null.

The following patch makes COALESCE and NULLIF first class expressions with
all the associated machinery.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> The following patch makes COALESCE and NULLIF first class expressions with
> all the associated machinery.

Patch applied.

This was your first backend patch, wasn't it?  Congratulations ---
a very fine effort!  I tweaked a couple of things I didn't like
stylistically, but overall it went in as-is.

            regards, tom lane