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 |/