Dynamic upsert in postgresql
- by Daniel
I have this upsert function that allows me to modify the fill_rate column of a row.
CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS '
DECLARE
num ALIAS FOR $1;
dat ALIAS FOR $2;
BEGIN
LOOP
-- First try to update.
UPDATE alarming SET fill_rate = dat WHERE equipid = num;
IF FOUND THEN
RETURN;
END IF;
-- Since its not there we try to insert the key
-- Notice if we had a concurent key insertion we would error
BEGIN
INSERT INTO alarming (equipid, fill_rate) VALUES (num, dat);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Loop and try the update again
END;
END LOOP;
END;
' LANGUAGE 'plpgsql';
Is it possible to modify this function to take a column argument as well? Extra bonus points if there is a way to modify the function to take a column and a table.
