Inventory Status
CASE + JOIN| Product | Category | Warehouse | Price | Stock | Reorder Pt | Status |
|---|---|---|---|---|---|---|
| Connecting... | ||||||
This query uses CASE expressions to classify inventory status dynamically.
The status changes in real-time as stock levels cross the reorder threshold.
When stock_qty drops to zero the row flips to "out of stock"; when it rises
above reorder_point it returns to "in stock" -- all computed server-side
and delivered as a delta over WebSocket.
-- Connect to the database
-- docker compose exec -T db psql -U postgres -d pg_reactive_test
-- Reduce stock below reorder point (triggers low_stock)
UPDATE inventory SET stock_qty = 3
WHERE product_id = 1;
-- Drain stock completely (triggers out_of_stock)
UPDATE inventory SET stock_qty = 0
WHERE product_id = 2;
-- Restock an item (back to in_stock)
UPDATE inventory SET stock_qty = 50
WHERE product_id = 2;
-- Add a new product + inventory
INSERT INTO products (name, price, category)
VALUES ('Wireless Charger', 29.99, 'electronics');
INSERT INTO inventory (product_id, stock_qty, reorder_point, warehouse)
VALUES (currval('products_id_seq'), 25, 10, 'warehouse-a');
-- Bulk restock everything
UPDATE inventory SET stock_qty = stock_qty + 20;