I have been playing around with making updatable views support MERGE,
and it looks to be fairly straightforward.
I'm intending to support auto-updatable views, WITH CHECK OPTION, and
trigger-updatable views, but not views with rules, as I think that
would be more trouble than it's worth.
Per the SQL standard, if the view isn't auto-updatable, it requires
the appropriate INSTEAD OF INSERT/UPDATE/DELETE triggers to perform
the merge actions. One limitation with the current patch is that it
will only work if the view is either auto-updatable with no INSTEAD OF
triggers, or it has a full set of INSTEAD OF triggers for all
INSERT/UPDATE/DELETE actions mentioned in the MERGE command. It
doesn't support a mix of those 2 cases (i.e., a partial set of INSTEAD
OF triggers, such as an INSTEAD OF INSERT trigger only, on an
otherwise auto-updatable view). Perhaps it will be possible to
overcome that limitation in the future, but I think that it will be
hard.
In practice though, I think that this shouldn't be very limiting -- I
think it's uncommon for people to define INSTEAD OF triggers on
auto-updatable views, and if they do, they just need to be sure to
provide a full set.
Attached is a WIP patch, which I'll add to the next CF. I still need
to do more testing, and update the docs, but so far, everything
appears to work.
Regards,
Dean
