SQLize | PHPize | SQLtest

CREATE TABLE FeedConsumption ( consumption_id SERIAL PRIMARY KEY, pigeon_id INT NOT NULL, feed_id INT NOT NULL, date DATE NOT NULL, quantity_grams DECIMAL(6,2) NOT NULL); CREATE TABLE FeedPurchase ( purchase_id SERIAL PRIMARY KEY, supplier_id INT NOT NULL, purchase_date DATE NOT NULL, quantity_kg DECIMAL(6,2) NOT NULL, cost DECIMAL(10,2) NOT NULL, feed_type VARCHAR(50) ); CREATE OR REPLACE PROCEDURE MergeAndUpdateFeedPurchase( v_today DATE, v_threshold NUMERIC ) LANGUAGE SQL AS $$ BEGIN WITH recent_purchases AS ( SELECT purchase_id, supplier_id, quantity_kg, cost, feed_type FROM FeedPurchase WHERE purchase_date > v_today - INTERVAL '30 days' ) MERGE INTO FeedPurchase AS target USING recent_purchases AS source ON target.purchase_id = source.purchase_id WHEN MATCHED THEN UPDATE SET cost = target.cost * 1.05 WHEN NOT MATCHED THEN INSERT (supplier_id, purchase_date, quantity_kg, cost, feed_type) VALUES (source.supplier_id, v_today, source.quantity_kg, source.cost, source.feed_type); END; $$;