Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbd32a25

Browse files
committed
> >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 andChristopher Kings-Lynne made most of the contributions, please findenclosed an example patch demonstrating an UPSERT-like capability.David Fetter
1 parent7cce39c commitbd32a25

File tree

1 file changed

+35
-1
lines changed

1 file changed

+35
-1
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.65 2005/04/07 14:53:04 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.66 2005/04/19 03:37:20 momjian Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -2103,6 +2103,40 @@ END;
21032103
don't use <literal>EXCEPTION</> without need.
21042104
</para>
21052105
</tip>
2106+
<example id="plpgsql-upsert-example">
2107+
<para>
2108+
This example uses an <literal>EXCEPTION</> to <command>UPDATE</> or
2109+
<command>INSERT</>, as appropriate.
2110+
2111+
<programlisting>
2112+
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
2113+
2114+
CREATE FUNCTION merge_db (key INT, data TEXT) RETURNS VOID AS
2115+
$$
2116+
BEGIN
2117+
LOOP
2118+
UPDATE db SET b = data WHERE a = key;
2119+
IF found THEN
2120+
RETURN;
2121+
END IF;
2122+
2123+
BEGIN
2124+
INSERT INTO db(a,b) VALUES (key, data);
2125+
RETURN;
2126+
EXCEPTION WHEN unique_violation THEN
2127+
-- do nothing
2128+
END;
2129+
END LOOP;
2130+
END;
2131+
$$
2132+
LANGUAGE plpgsql;
2133+
2134+
SELECT merge_db (1, 'david');
2135+
SELECT merge_db (1, 'dennis');
2136+
</programlisting>
2137+
2138+
</para>
2139+
</example>
21062140
</sect2>
21072141
</sect1>
21082142

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp