SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Storage ( Storage_key SERIAL PRIMARY KEY, Quantity INTEGER CHECK (Quantity > 0) NOT NULL, Delivery_Date DATE CHECK (Delivery_Date <= CURRENT_DATE) NOT NULL, Price DECIMAL(10, 2) NOT NULL, Consumed_date DATE CHECK (Consumed_date >= CURRENT_DATE) NOT NULL ); CREATE TABLE Sold_Medicines ( Sold_key SERIAL PRIMARY KEY, Quantity int CHECK (Quantity > 0) NOT NULL, Sale_Date DATE CHECK (Sale_Date <= CURRENT_DATE) NOT NULL, Storage_key INT NOT NULL, FOREIGN KEY (Storage_key) REFERENCES Storage(Storage_key) ON UPDATE CASCADE ON DELETE RESTRICT ); INSERT INTO Storage (Quantity, Delivery_Date, Price, Consumed_date) VALUES (100, '2023-04-10', 20.00, '2023-05-10'); INSERT INTO Storage (Quantity, Delivery_Date, Price, Consumed_date) VALUES (50, '2023-04-12', 15.00, '2023-05-15'); INSERT INTO Storage (Quantity, Delivery_Date, Price, Consumed_date) VALUES (75, '2023-02-15', 25.00, '2023-05-20'); INSERT INTO Storage (Quantity, Delivery_Date, Price, Consumed_date) VALUES (200, '2023-02-16', 30.00, '2023-05-16'); INSERT INTO Storage (Quantity, Delivery_Date, Price, Consumed_date) VALUES (125, '2023-02-17', 18.00, '2023-05-18'); INSERT INTO Sold_Medicines (Quantity, Sale_Date, Storage_key) VALUES (20, '2023-04-13', 1); INSERT INTO Sold_Medicines (Quantity, Sale_Date, Storage_key) VALUES (30, '2023-04-14', 1); INSERT INTO Sold_Medicines (Quantity, Sale_Date, Storage_key) VALUES (40, '2023-04-15', 2); INSERT INTO Sold_Medicines (Quantity, Sale_Date, Storage_key) VALUES (10, '2023-04-15', 2); DROP FUNCTION sell_medicine(integer,integer); CREATE OR REPLACE FUNCTION sell_medicine( storage_key INTEGER, quantity INTEGER ) RETURNS VOID AS $$ DECLARE storage_quantity INTEGER; BEGIN SELECT Quantity INTO STRICT storage_quantity FROM Storage WHERE Storage_key = sell_medicine.storage_key; IF storage_quantity < quantity THEN RAISE EXCEPTION 'Недостаточно товара на складе'; END IF; UPDATE Storage SET Quantity = Quantity - quantity WHERE Storage_key = sell_medicine.storage_key; INSERT INTO Sold_Medicines (Quantity, Sale_Date, Storage_key) VALUES (quantity, CURRENT_DATE, sell_medicine.storage_key); END; $$ LANGUAGE plpgsql; SELECT sell_medicine(1, 10);