Postgresql : Achieve check constraint on aggregated SUM

Question

So i have this inventory db

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=19946dda5ca1ab86e9a8cf63717e65fa

Which more or less adds inventories from various sources (inventory_type) and will have a products(id) foreign key reference. According to which the sum would be calculated.

The predicated should be

If the stock value is in negative then it shouldn't be greater than the stock itself.

How would you suggest me to achieve it ?

Here is what i tried.

Check doesn't allow subqueries so i settled for triggers.

 CREATE FUNCTION public.check_stock_quantity()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
BEGIN
   IF NEW.stock < 0 THEN
       IF ((SELECT SUM(stock) FROM inventory WHERE id= NEW.id) - NEW.stock ) < 0 THEN
            RAISE EXCEPTION 'Not enough stocks. Invalid stock entry';
        END IF;
   END IF;

   RETURN NEW;
END;
$BODY$;

And then i applied it

stock_trigger BEFORE INSERT OR UPDATE ON inventory FOR EACH ROW EXECUTE PROCEDURE check_stock_quantity()

But it refuses to work.

Is there a better way to handle such an inventory ? If no then what is wrong with my triggers ?

In what way does it not work? Be specific.

shawnt00
– shawnt00

2019-08-22 20:14:09 +00:00
Commented Aug 22, 2019 at 20:14 — shawnt00
– shawnt00, Commented Aug 22, 2019 at 20:14
EDIT :- it worked

Élisa Plessis
– Élisa Plessis

2019-08-23 01:53:15 +00:00
Commented Aug 23, 2019 at 1:53 — Élisa Plessis
– Élisa Plessis, Commented Aug 23, 2019 at 1:53

klin · Accepted Answer · 2019-08-22 20:37:03Z

1

First, the new stock should be added to (not subtracted from) the sum of stocks in the table. Next, you probably mean pid as the grouping column, as id is a primary key. So the function body should look like this:

    IF NEW.stock < 0 THEN
        IF ((SELECT SUM(stock) FROM inventory WHERE pid= NEW.pid) + NEW.stock ) < 0 THEN
            RAISE EXCEPTION 'Not enough stocks. Invalid stock entry';
        END IF;
    END IF;

    RETURN NEW;

answered Aug 22, 2019 at 20:37

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Postgresql : Achieve check constraint on aggregated SUM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related