On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote:
> On 06/08/10 10:12, Simon Riggs wrote: > > So DO NOTHING is the default and implies silently ignoring rows. RAISE > > ERROR is the opposite. > > > > Coding for those seems very easy, its just a question of "should we do > > it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2 > > introduction of AND clauses, and SQL:2011 has so far followed the DB2 > > introduction of DELETE action also. > > I see neither DO NOTHING or RAISE ERROR in the documentation of DB2, > Oracle, or MSSQL server.
Agreed, Oracle and MSSQL server does not have these.
However, DB2 very clearly does have these features
* SIGNAL which raises an error and can be used in place of any action, at any point in sequence of WHEN clauses. DB2 already supports SIGNAL as part of SQL/PSM, which we do not, so RAISE ERROR was the nearest equivalent command for PostgreSQL.
* ELSE IGNORE which does same thing as DO NOTHING, except it must always be last statement in a sequence of WHEN clauses. DO NOTHING is already a phrase with exactly this meaning in PostgreSQL, so I suggest that.
So, we need to add both DO NOTHING and RAISE ERROR actions in the MERGE command now !? What will RAISE ERROR do? To stop the whole MERGE command OR, just throw an error notice for the row and move on.
-- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services
