Skip site navigation (1) Skip section navigation (2)

Re: COALESCE implementation question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: pgsql-hackers(at)hub(dot)org
Subject: Re: COALESCE implementation question
Date: 2000-08-06 02:36:14
Message-ID: 15355.965529374@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> I realize that the standard says:

>     2) COALESCE (V(1), V(2)) is equivalent to the following <case
>        specification> :
>            CASE WHEN V(1) IS NOT NULL THEN V(1) ELSE V(2) END

> I was wondering if there was a reason that we interpret this literally,
> rather than implement a function?

Well, the standard is perfectly clear, isn't it?  If V(1) has side
effects then trying to optimize this into just one evaluation of V(1)
will generate non-spec-compliant results.

I'd have to agree that two evaluations are pretty annoying, though,
and I wonder whether the spec authors *really* meant to demand
double evaluation of the "winning" case item.  Can anyone check
whether Oracle and other DBMSes perform double evaluation?

BTW, the "BETWEEN" expression has exactly the same issue.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-08-06 02:37:10
Subject: Re: LIKE/ESCAPE implementation
Previous:From: Tom LaneDate: 2000-08-06 02:27:37
Subject: Re: COALESCE implementation question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group