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.

Daniel Winterstein · Accepted Answer · 2011-11-18 15:33:06Z

3

As an alternative approach, you can do an upsert without a function by using an insert + update with where clauses to make them only succeed in the right case. E.g.

update mytable set col1='value1' where (col2 = 'myId');
insert into mytable select 'value1', 'myId' where not exists (select 1 from mytable where col2='myId');

Which would avoid having lots of custom postgres specific functions.

answered Nov 18, 2011 at 15:33

Daniel Winterstein

2,5851 gold badge30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Van Dyk Over a year ago

This won't work if two transactions try to insert a row at the same time. Unless you use serializable transactions.

baash05 Over a year ago

and it doesn't allow postgres to optimise for the function.

leonbloy · Accepted Answer · 2010-06-07 18:12:44Z

1

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

answered Jun 7, 2010 at 18:12

leonbloy

76.5k22 gold badges149 silver badges197 bronze badges

Comments

mu is too short · Accepted Answer · 2011-05-18 15:36:04Z

0

Maybe a simpler approach, just less line ;)

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
    IF NOT FOUND THEN
    INSERT INTO tableName values (value, arg1, arg2);
    END IF;
END;
$$ LANGUAGE 'plpgsql';

edited May 18, 2011 at 15:36

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered May 18, 2011 at 9:44

Arohi

111 bronze badge

Collectives™ on Stack Overflow

Dynamic upsert in postgresql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related