<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="" text="#000000">
I'm intrigued by this solution, Johan. It might be just the ticket!
I'll do some benchmarks when I have time in a week or so.<br>
<br>
Johan Nel wrote:
<blockquote cite="mid:gtfhei$gs9$1(at)news(dot)motzarella(dot)org" type="cite">Adam
B wrote:
<br>
<blockquote type="cite">Hello all,
<br>
<br>
Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?
<br>
</blockquote>
>From the help files maybe the following could get you on the right
track:
<br>
<br>
This example uses exception handling to perform either UPDATE or
INSERT, as appropriate:
<br>
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
<br>
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
<br>
$$
<br>
BEGIN
<br>
LOOP
<br>
-- first try to update the key
<br>
UPDATE db SET b = data WHERE a = key;
<br>
IF found THEN
<br>
RETURN;
<br>
END IF;
<br>
-- not there, so try to insert the key
<br>
-- if someone else inserts the same key concurrently,
<br>
-- we could get a unique-key failure
<br>
BEGIN
<br>
INSERT INTO db(a,b) VALUES (key, data);
<br>
RETURN;
<br>
EXCEPTION WHEN unique_violation THEN
<br>
-- do nothing, and loop to try the UPDATE again
<br>
END;
<br>
END LOOP;
<br>
END;
<br>
$$
<br>
LANGUAGE plpgsql;
<br>
<br>
HTH,
<br>
<br>
Johan Nel
<br>
Pretoria, South Africa.
<br>
<br>
</blockquote>
<br>
<BR />
<BR />
<HR />
Videx Inc. 1105 N. E. Circle Blvd. Corvallis OR 97330 (541) 758-0521<BR />
CONFIDENTIAL COMMUNICATION: The email message and any attachments are intended only for the addressee. They may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, any dissemination, distribution, or copying is expressly prohibited. If you received this email message in error, please notify the sender immediately by replying to this e-mail message or by telephone<BR />
</body>
</html>