Dynamic upsert in postgresql

Posted by Daniel on Stack Overflow See other posts from Stack Overflow or by Daniel
Published on 2010-06-07T16:18:30Z Indexed on 2010/06/07 16:22 UTC
Read the original article Hit count: 221

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about dynamic