COALESCE implementation question

I noticed that the COALESCE function is implemented as a case statement,
with the result that:
  update t1 set f = Coalesce( (select fn from t2 x where x.f1 = t1.f1),
t1.f1)

has the following plan:

Seq Scan on t1  (cost=0.00..20.00 rows=1000 width=10) SubPlan   ->  Seq Scan on t2 x  (cost=0.00..22.50 rows=10
width=4)  ->  Seq Scan on t2 x  (cost=0.00..22.50 rows=1000 width=4)
 


ie. it *seems* to scan t2 twice, because the resulting CASE statement for
the subselect is:
   case when not (select fn from t2 x where x.f1 = t1.f1) is NULL then           (select fn from t2 x where x.f1 =
t1.f1)else           t1.f1   end
 

which does seem to imply two executions of the same select statement.

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
   3) "COALESCE (V(1), V(2), . . . , V(n))", for n >= 3, is
equivalent      to the following <case specification>:
          CASE
WHEN V(1) IS NOT NULL THEN V(1)           ELSE COALESCE (V(2), . . . , V(n))           END

I was wondering if there was a reason that we interpret this literally,
rather than implement a function? Or set a flag on the CaseExpr node to
indicate that the 'result == whenClause', or some such.

I am still hunting through the planner/optimizer to try to understand if
this is feasible, and would appreciate any suggestions...


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/