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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
