Dynamic upsert in postgresql

Question

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.

Answer 1

You want to read about dynamic commands in plsql. Just build your query and invoke EXECUTE.