I'm intrigued by this solution, Johan.=C2=A0 It might be just the ticket!=
=C2=A0
I'll do some benchmarks when I have time in a week or so.
Johan Nel wrote:
Adam
B wrote:
Hello all,
Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?
=46rom the help files maybe the following could get you on the right
track:
This example uses exception handling to perform either UPDATE or
INSERT, as appropriate:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
=C2=A0=C2=A0=C2=A0 LOOP
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- first try to update the key
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 UPDATE db SET b =3D data WHERE a=
=3D key;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 IF found THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END IF;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- not there, so try to insert t=
he key
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- if someone else inserts the s=
ame key concurrently,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- we could get a unique-key fai=
lure
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 BEGIN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 INSERT I=
NTO db(a,b) VALUES (key, data);
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 EXCEPTION WHEN unique_violation =
THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- do no=
thing, and loop to try the UPDATE again
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END;
=C2=A0=C2=A0=C2=A0 END LOOP;
END;
$$
LANGUAGE plpgsql;
HTH,
Johan Nel
Pretoria, South Africa.
Videx Inc. 1105 N. E. Circle Blvd. Corva=
llis OR 97330 (541) 758-0521
CONFIDENTIAL COMMUNICATION: The email message and&=
nbsp;any attachments are intended only for th=
e addressee. They may be privileged, co=
nfidential, and protected from disclosure. If =
;you are not the intended recipient, any =
;dissemination, distribution, or copying is expres=
sly prohibited. If you received this em=
ail message in error, please notify the =
sender immediately by replying to this e-mail=
message or by telephone