From bd32a2559873030020c7b4324ba2fbb4026d43d5 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Tue, 19 Apr 2005 03:37:20 +0000
Subject: [PATCH] > >Luckily, PG 8 is available for this.  Do you have a short
 example? > > No, and I think it should be in the manual as an example. > >
 You will need to enter a loop that uses exception handling to detect >
 unique_violation.

Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.

David Fetter
---
 doc/src/sgml/plpgsql.sgml | 36 +++++++++++++++++++++++++++++++++++-
 1 file changed, 35 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9cb0ad2a8b..0d359b1d3b 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.65 2005/04/07 14:53:04 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.66 2005/04/19 03:37:20 momjian Exp $
 -->
 
 <chapter id="plpgsql"> 
@@ -2103,6 +2103,40 @@ END;
       don't use <literal>EXCEPTION</> without need.
      </para>
     </tip>
+    <example id="plpgsql-upsert-example">
+    <para>
+    This example uses an <literal>EXCEPTION</> to <command>UPDATE</> or
+    <command>INSERT</>, as appropriate.
+
+<programlisting>
+CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
+
+CREATE FUNCTION merge_db (key INT, data TEXT) RETURNS VOID AS
+$$
+BEGIN
+    LOOP
+        UPDATE db SET b = data WHERE a = key;
+        IF found THEN
+            RETURN;
+        END IF;
+
+        BEGIN
+            INSERT INTO db(a,b) VALUES (key, data);
+            RETURN;
+        EXCEPTION WHEN unique_violation THEN
+            -- do nothing
+        END;
+    END LOOP;
+END;
+$$
+LANGUAGE plpgsql;
+
+SELECT merge_db (1, 'david');
+SELECT merge_db (1, 'dennis');
+</programlisting>
+
+    </para>
+    </example>
   </sect2>
   </sect1>
 
-- 
2.11.0