Insert Or Update ? une solution¶

We have a table dummy with primary key key

Add the following rule on insert:

CREATE RULE replace_dummy AS
  ON INSERT TO dummy
  WHERE
    EXISTS(SELECT 1 FROM dummy WHERE key = NEW.key)
  DO INSTEAD
     (UPDATE dummy SET value = NEW.value);

Use the function:

CREATE FUNCTION merge_dummy(ikey int, ivalue text) RETURNS VOID AS
$$
BEGIN
    UPDATE dummy SET value = ivalue WHERE key = ikey;
    IF found THEN
        RETURN;
    END IF;
    INSERT INTO dummy VALUES (ikey, ivalue);
    RETURN;
END;
$$
LANGUAGE plpgsql;