Hi,
is there an elegant way to tell PG :

a) Hey PG, look here are e.g. 3 values A, B, c for tableX
b) please check if there is a row matching A and B as key in tableX
c) if such a row exists, execute an UPDATE on column c else INSERT a
new row.

Currently I have a trigger function that should store a value in tableX
whenever a certain column in tableY gets changed.
I do it with:
a) delete from tableX where key = ( A, B ) ( regardless if there is one )
b) insert into tableX

This seems not very efficient though it works.
Is there a better way?

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Efficiency matters only if the solution is correct, and that's not the
case of your function - there's a quite trivial race condition. Imagine
there are two transactions running at the same time, executing the
function concurrently.

The first one will succeed, while the other one will fail because of
unique constraint violation. A correct solution is something like this

BEGIN
INSERT INTO ...
EXCEPTION
-- the key already exists, so let's update
WHEN unique_violation THEN
UPDATE ...
END;

Regarding efficiency - I'm not aware of a better solution. There are plans
to implement true MERGE but that's in the future. All you can do right now
is to make sure the key is indexed (I guess it's a PK anyway) so that the
INSERT/UPDATE are fast.

Well, actually there's one other thing you could do - you can do a BEFORE
INSERT trigger that checks if the key already exists, and in case it does
switch to UPDATE. Something like

CREATE OR REPLACE FUNCTION my_trigger() RETURNS trigger as $$
BEGIN
UPDATE my_table SET .... WHERE key = NEW.key;
IF (FOUND) THEN
-- updated, do not execute the insert
RETURN NULL;
END IF;

-- execute the insert
RETURN NEW;
END;
$$ language plpgsql;

CREATE TRIGGER merge_trigger BEFORE INSERT ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_trigger();

This is probably more elegant - just execute INSERT statements and it will
handle all the work. The only problem is it does not report the number of
updated rows (it just returns 0 in that case).

But generally it's just as efficient as the solution described above.

regards
Tomas


--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql