On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
> >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.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!
? upsert.diff
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.61
diff -c -r1.61 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml   14 Feb 2005 00:54:26 -0000      1.61
--- doc/src/sgml/plpgsql.sgml   8 Mar 2005 08:19:31 -0000
***************
*** 2003,2008 ****
--- 2003,2042 ----
        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>
  
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to