From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200405112023.i4BKNIx25097@candle.pha.pa.us>
Subject: Re: Adding MERGE to the TODO list (resend with subject)
In-Reply-To: <c4Mnc.31976$LA4.16535@edtnps84>
To: Bricklen <bricklen-rem@yahoo.comz>
Date: Tue, 11 May 2004 16:23:18 -0400 (EDT)
Cc: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


OK, DELETE added:

	* Add MERGE command that does UPDATE/DELETE, or on failure,
	  INSERT (rules, triggers?)


---------------------------------------------------------------------------

Bricklen wrote:
> Alvaro Herrera wrote:
> 
> <snip>
> > Oh, I see.  Complex stuff ... I wonder how will it work with sequences
> > -- if one insertion fails and we have to try again, there's a chance a
> > sequence could be advanced more than once.  Note the article skips the
> > "signal-statement" symbol (is it present in SQL99? What does it do?)
> > 
> > I also wonder if there will be a corresponding RULE implementation ...
> > 
> > The full DB2 reference is at
> > http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0010873.htm
> > 
> > (signal-statement is something to raise an exception, apparently)
> > 
> > (I wonder why they don't use BNF syntax anymore ...)
> > 
> Just to add to this information, Oracle 9i and 10g have also implemented 
> the MERGE command. 9i offers an update/insert, whereas 10g adds a delete 
> option as well, which is rather handy.
> 'Purpose', quoted from: 
> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_9016.htm#sthref7014
> 
> (note, to view this link, you will need to sign up for a free OTN acct.)
> 
> "Use the MERGE statement to select rows from one or more sources for 
> update or insertion into one or more tables. You can specify conditions 
> to determine whether to update or insert into the target tables.
> 
> This statement is a convenient way to combine multiple operations. It 
> lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
> 
> MERGE is a deterministic statement. That is, you cannot update the same 
> row of the target table multiple times in the same MERGE statement."
> 
> Point being, I've found the delete option very useful too, rather than 
> having to do the same procedurally.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073