Postgresql query to get inventory from different locations

Question

I have this table:

item_id	quantity	location_id	stock
1	3	11	2
1	3	22	9

i.e the item_id 1, has a total of 3 items, I need to take those 3 items from the stock of the two location (11, 12), it means that the result should be looks like this:

item_id	quantity	location_id	stock	committed
1	3	11	0	2
1	3	22	8	1

I've been trying to use window functions like: over and partition by but I'm not sure how to accumulate values, if anyone have an idea how I can solve this I really appreciate thanks!

Answer 1

I've created the following tables and populated them to be consistent with the sample input presented in the original post:

CREATE TABLE items (
  id integer PRIMARY KEY
);

CREATE TABLE locations (
  id integer PRIMARY KEY
);

CREATE TABLE items_locations (
  item_id integer REFERENCES items(id),
  location_id integer REFERENCES locations(id),
  stock integer,
  CONSTRAINT items_locations_pk PRIMARY KEY (item_id, location_id)
);

INSERT INTO items (id)
VALUES (1);

INSERT INTO locations (id)
VALUES (11), (22);

INSERT INTO items_locations (item_id, location_id, stock)
VALUES (1, 11, 2), (1, 22, 9);

The following SQL performs the operations described in the original post:

WITH
  parms(item_id, quantity) AS (
    VALUES (1, 3)),
  commitments AS (
    SELECT il.item_id,
           il.location_id,
           GREATEST(0,
                    LEAST(il.stock, parms.quantity - SUM(il.stock)
                          OVER (PARTITION BY il.item_id
                                ORDER BY il.location_id
                                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))) AS committed
      FROM parms
      JOIN items_locations il
        ON il.item_id = parms.item_id),
  updated_rows AS (
    UPDATE items_locations il
      SET stock = il.stock - c.committed
      FROM commitments c
      WHERE c.committed > 0
        AND il.item_id = c.item_id
        AND il.location_id = c.location_id
     RETURNING il.*, c.committed)
  SELECT ur.item_id, parms.quantity, ur.location_id, ur.stock, ur.committed
    FROM parms
    JOIN updated_rows ur
      ON ur.item_id = parms.item_id
  ORDER BY ur.item_id, ur.location_id;

The first CTE, parms, defines the items and desired quantities. The amount of stock to be taken from each location for each item is calculated in commitments. The arguments to LEAST ensure that no more than the available stock is taken. GREATEST is used to ensure that no stock is taken once the desired quantity has been committed. The stock for each item in each location is adjusted by the committed amount and the updated rows along with the associated committed amounts are returned as updated_rows. The final SELECT combines parms with updated_rows to return the output as shown in the original post.

This query does not include logic to ensure that adequate stock is available to fulfill the requested quantity.