DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=VxBA4UqXbl7VHJJfg5pQc8QpVQz4GPoI16Pfbo1breqgeO4xXVJ8oe+ObYXHcmBB0pFIUs3iD5W5GQxzSulKL4Y7/AUlOkLO/wDqbVfT1SlTPIZVn+US8661OWVLak6/0J54+J+ICu1yPVCLgzK3IpRmr0f4YsqNUGvlhuhardc=
Message-ID: <a55915760704191158l278c760bg3b95296535a79e07@mail.gmail.com>
Date: Thu, 19 Apr 2007 13:58:59 -0500
From: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Doing a conditional insert/update
Cc: "Markus Holzer" <holli.holzer@googlemail.com>
In-Reply-To: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>

On 4/19/07, Markus Holzer <holli.holzer@googlemail.com> 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