Robert Haas <robertmh...@gmail.com> wrote: > Anything we do about triggers will by definition be novel. Right > now, we have INSERT, UPDATE, and DELETE. If we add a new > operation, whether it's called UPSERT or MERGE or FROB, [ ... ]
If we call it MERGE, then we had better follow the rules the standard lays out for triggers on a MERGE statement -- which is that UPDATE triggers fire for rows updated and that INSERT triggers fire for rows inserted. That kinda makes sense, since the MERGE command is almost like a row-by-row CASE statement allowing one or the other. If we make up our own command verb, we are free to do as we like. > Maybe something like this: > > INSERT INTO targettable(key, quantity, inserted_at) > VALUES(123, quantity, now()) > ON DUPLICATE (key) > UPDATE SET quantity = quantity + OLD.quantity, updated_at = now(); That seems a lot cleaner than the proposal on the Wiki page. If we go that route, it makes sense to fire the BEFORE INSERT triggers before attempting the insert and then fire BEFORE UPDATE triggers before attempting the UPDATE. If either succeeds, I think we should fire the corresponding AFTER triggers. We already allow a BEFORE triggers to run and then omit the triggering operation without an error, so I don't see that as a problem. This makes a lot more sense to me than attempting to add a new UPSERT trigger type. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
