CODEWITHSUNDEEP
sqlpostgresqltriggersaggregate-functions
Élisa Plessis
Élisa Plessis

Reputation: 101

Postgresql : Achieve check constraint on aggregated SUM

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 ?

Upvotes: 1

Views: 777

Answers (1)

klin
klin

Reputation: 121604

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;

Upvotes: 1

Related Questions