create_add_missing_stocks.sql 1.3 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
  1. CREATE OR REPLACE FUNCTION add_missing_stocks() RETURNS void AS
  2. $$
  3. DECLARE
  4. proCursor CURSOR FOR SELECT pro_id FROM Product;
  5. proId Product.pro_id%TYPE;
  6. warCursor CURSOR FOR SELECT war_id FROM Warehouse;
  7. warId Warehouse.war_id%TYPE;
  8. stoCursor CURSOR FOR SELECT sto_quantity FROM Stock WHERE pro_id = proId AND war_id = warId;
  9. stoQuantity Stock.sto_quantity%TYPE;
  10. BEGIN
  11. RAISE NOTICE 'Start';
  12. -- Iterate on products
  13. OPEN proCursor;
  14. LOOP
  15. FETCH proCursor INTO proId;
  16. EXIT WHEN proId IS NULL;
  17. -- Iterate on warehouses
  18. OPEN warCursor;
  19. LOOP
  20. FETCH warCursor INTO warId;
  21. EXIT WHEN warId IS NULL;
  22. -- Find associated storage
  23. OPEN stoCursor;
  24. FETCH stoCursor INTO stoQuantity;
  25. IF stoQuantity IS NULL
  26. THEN
  27. RAISE NOTICE 'Pro % for war % not available', proId, warId;
  28. INSERT INTO Stock(pro_id, war_id) VALUES (proId, warId);
  29. RAISE NOTICE '- Pro % for war % was inserted', proId, warId;
  30. ELSE
  31. RAISE NOTICE 'Pro % for war % stores %', proId, warId, stoQuantity;
  32. END IF;
  33. CLOSE stoCursor;
  34. END LOOP;
  35. CLOSE warCursor;
  36. END LOOP;
  37. CLOSE proCursor;
  38. RAISE NOTICE 'End';
  39. END;
  40. $$
  41. LANGUAGE plpgsql;