Implement TRIGGER/RULE in postgresql to deduplicate records-CodePudding

I have a classic cart items table in PostgreSQL.

What I want:

when I perform an insert I want to prevent inserting a new duplicated record but rather update the quantity field for existing record.

Tried trigger function and trigger before insert:

CREATE OR REPLACE FUNCTION ecommerce.add_store_cart_item()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    VOLATILE
    COST 100
AS $BODY$
begin
 insert into ecommerce.store_cart_item 
  (item_id, user_id) 
  values 
  (NEW.item_id, NEW.user_id) 
  on conflict (item_id, user_id) DO UPDATE
   SET qty = EXCLUDED.qty   NEW.qty;
  return NULL;
end;
$BODY$;

But I'm getting recursion here.

Tried also rule but it doesn't work either:

CREATE OR REPLACE RULE check_store_cart_item_duplicates AS
    ON INSERT TO ecommerce.store_cart_item
    WHERE (EXISTS ( SELECT 1
           FROM ecommerce.store_cart_item store_cart_item_1
          WHERE ((store_cart_item_1.item_id = new.item_id) AND (store_cart_item_1.user_id = new.user_id))))
    DO INSTEAD
(UPDATE ecommerce.store_cart_item SET qty = (store_cart_item.qty   new.qty)
  WHERE ((store_cart_item.user_id = new.user_id) AND (store_cart_item.item_id = new.item_id)));

Note

I don't want to use the simple INSERT ... ON CONFLICT command as I'd like it to be integrity check on the database level (this approach actually moves it to the application level).

I would like to know the most efficient and correct way.

CodePudding user response：

The law is there to serve the people, not the other way around.

If your rule to code this inside the database keeps you from using the best and most efficient solution (INSERT ... ON CONFLICT), ignore the rule in this case. Don't let a guideline get in the way of a simple and good solution.

CodePudding user response：

Use an UPDATE statement instead of your INSERT statement inside the trigger. Set your trigger to fire on INSERTs only.

e.g.

UPDATE ecommerce.store_cart_item
SET qty = qty   1
WHERE user_id = NEW.user_id AND item_id = NEW.item_id;

RETURN NULL; /* Stop the INSERT */