On 4/19/07, Markus Holzer <[EMAIL PROTECTED]> wrote:
Hello.
I'm currently developing my first web app with Postgres and I have a
question.
How do I perform a conditional insert/update?
To clarify: I need to insert data into a table when the primary key is not
already in the table, or an update if it is. I have currently solved this by
SELECTing for the primary key, then looking if there is a row, and if there
is I do an UPDATE otherwise I do an INSERT. But since this is a web app this
way of course leaves a big race condition.
Is there a way to get around that? Like the ON DUPLICATE KEY UPDATE
statement in MySQL?
Thanks for your time,
Holli
--------------------------------------------------
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');
--------------------------------------------------
Straight from the docs:
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
