REPLACE implementation (was: Re: MERGE vs REPLACE)

On 11/12/05, Matteo Beccati <php@beccati.com> wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.

Normally I'd plump for following the standard ... but AFAIR, we have had
bucketloads of requests for REPLACE functionality, and not one request
for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
whole lot harder and slower than REPLACE, it seems that we could do
worse than to concentrate on doing REPLACE for now. (We can always come
back to MERGE some other day.)

I would also like to add that MySQL's REPLACE is not exactly an INSERT
OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the
fields not specified in the query are set to their defaults:

This sounds a lot like postgres implementation of UPDATE... delete
tuple (actually, mark it as dead and insert)...

Maybe we can use this? or maybe some kind of merge between ExecDelete
and ExecInsert?

Also, the MySQL implementation require DELETE and INSERT permission.
What about triggers? run before/after delete and insert?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)