Changes between Version 1 and Version 2 of FunctionsProceduresTriggers

Timestamp:

07/01/26 16:55:17 (9 days ago)

Author:

231561

Comment:

--

FunctionsProceduresTriggers

@@ -3 +3 @@
 == Overview of implemented objects
 
-|| Type || Name || Description || Step ||
-|| Trigger || trg_appointment_status_audit ||
-|| Function || fn_get_active_diagnoses ||
-|| Function || fn_prescribe_medicine ||
-|| Function || fn_check_doctor_availability ||
-|| Procedure || prc_create_referral ||
-|| Procedure || prc_add_appointment_for_referral ||
-|| Procedure || prc_complete_examination ||
-|| Procedure || prc_add_new_diagnosis ||
-|| Procedure || prc_create_prescription ||
-|| Procedure || prc_buy_prescription ||
+||= Type =||= Name =||= Description =||= Step =||
+||=Trigger=|| trg_appointment_status_audit || Logs every change in appointment status || - ||
+||=Function=|| fn_get_active_diagnoses || Returns active diagnoses for patient || before 5 ||
+||=Function=|| fn_prescribe_medicine || Validates and returns data for prescription || in 5 ||
+||=Function=|| fn_check_doctor_availability || Checks doctor availability for date || in 2 ||
+||=Procedure=|| prc_create_referral || Creates a referral || 1 ||
+||=Procedure=|| prc_add_appointment_for_referral || Schedules an appointment after a referral || 2 ||
+||=Procedure=|| prc_complete_examination || Completes an appointment, creates an examination || 3 ||
+||=Procedure=|| prc_add_new_diagnosis || Adds a diagnosis after an examination || 4 ||
+||=Procedure=|| prc_create_prescription || Creates a prescription for a diagnosis || 5 ||
+||=Procedure=|| prc_buy_prescription || Dispenses a medicine at a pharmacy || 6 ||
+
+== Application workflow
+
+||= Step =||= Description =||
+||=1=|| Doctor refers patient to department ||
+||=2=|| Schedule appointment by referral ||
+||=3=|| Complete appointment + create examination ||
+||=4=|| Doctor adds diagnosis by examination ||
+||=5=|| Doctor issues prescription for diagnosis ||
+||=6=|| Patient picks up medication at pharmacy ||
+
+=== Trigger 1 : trg_appointment_status_audit
+
+The trigger is automatically fired after every UPDATE on the Appointment table. When the old and new status values ​​differ, a new record is inserted into appointment_status_audit with the old status, the new status, the time of change, and the user. This provides a complete history of appointment changes without any additional logic in the application — the trigger is transparent and fires both on direct UPDATE operations and when calling prc_complete_examination.
+
+**Supporting table:**
+
+CREATE TABLE IF NOT EXISTS appointment_status_audit ( 
+
+    audit_id        SERIAL PRIMARY KEY, 
+
+    appointment_id  INT NOT NULL, 
+
+    old_status      VARCHAR(30), 
+
+    new_status      VARCHAR(30), 
+
+    changed_at      TIMESTAMP NOT NULL DEFAULT NOW(), 
+
+    changed_by      TEXT DEFAULT CURRENT_USER 
+
+); 
+
+**Implementation:**
+
+CREATE OR REPLACE FUNCTION fn_audit_appointment_status() 
+
+RETURNS TRIGGER AS $$ 
+
+BEGIN 
+
+    IF OLD.status IS DISTINCT FROM NEW.status THEN 
+
+        INSERT INTO appointment_status_audit (appointment_id, old_status, new_status, changed_at, changed_by) 
+
+        VALUES (NEW.appointment_id, OLD.status, NEW.status, NOW(), CURRENT_USER); 
+
+    END IF; 
+
+    RETURN NEW; 
+
+END; 
+
+$$ LANGUAGE plpgsql; 
+
+CREATE TRIGGER trg_appointment_status_audit 
+
+    AFTER UPDATE ON Appointment 
+
+    FOR EACH ROW 
+
+    EXECUTE FUNCTION fn_audit_appointment_status(); 
+
+=== Function 1 : fn_get_active_diagnoses
+
+The function returns a tabular result with all active diagnoses for a given patient. A diagnosis whose date_to is NULL or is in the future is active. For each diagnosis, the ICD code, description, whether it is primary, and the name of the doctor who prescribed it are returned. Before prescribing a medication, the application calls this function to show the doctor for which diagnosis it is prescribing.
+
+**Implementation:**
+
+CREATE OR REPLACE FUNCTION fn_get_active_diagnoses(p_patient_id INT) 
+
+RETURNS TABLE (patient_diagnosis_id INT, diagnosis_name VARCHAR(255), 
+
+               icd_code VARCHAR(20), icd_description VARCHAR(255), 
+
+               is_primary BOOLEAN, date_from DATE, date_to DATE, 
+
+               doctor_name TEXT) AS $$ 
+
+BEGIN 
+
+    IF NOT EXISTS (SELECT 1 FROM Patient WHERE patient_id = p_patient_id) THEN 
+
+        RAISE EXCEPTION 'Patient with ID % does not exist.', p_patient_id; 
+
+    END IF; 
+
+    RETURN QUERY 
+
+        SELECT pd.patient_diagnosis_id, pd.diagnosis_name, 
+
+               i.code, i.description, pd.is_primary, 
+
+               pd.date_from, pd.date_to, 
+
+               (d.first_name | | ' ' | | d.last_name)::TEXT 
+
+        FROM Patient_diagnosis pd 
+
+        JOIN ICD    i ON i.icd_id    = pd.icd_id 
+
+        JOIN Doctor d ON d.doctor_id = pd.doctor_id 
+
+        WHERE pd.patient_id = p_patient_id AND (pd.date_to IS NULL OR pd.date_to >= CURRENT_DATE) 
+
+        ORDER BY pd.is_primary DESC, pd.date_from DESC; 
+
+END; $$ LANGUAGE plpgsql; 
+
+=== Function 2 : fn_prescribe_medicine
+
+The function is the central validation logic for prescribing medications. It checks that the patient and doctor exist, that the diagnosis belongs to the patient and is active, that the ATC code exists, that the dosage and duration are positive, and that there is sufficient stock for that ATC code. If all hecks pass, it returns a single row with all the data needed to create a prescription. The prc_create_prescription procedure calls this function and directly uses the returned values.
+
+**Implementation:**
+
+CREATE OR REPLACE FUNCTION fn_prescribe_medicine( 
+
+    p_patient_id INT, p_doctor_id INT, 
+
+    p_patient_diagnosis_id INT, p_atc_id INT, 
+
+    p_dosage INT, p_duration INT 
+
+) 
+
+RETURNS TABLE (patient_id INT, doctor_id INT, 
+
+               patient_diagnosis_id INT, atc_id INT, 
+
+               inventory_id INT, dosage INT, duration INT, 
+
+               presc_date DATE, available_stock INT, 
+
+               drug_name TEXT) AS $$ 
+
+DECLARE 
+
+    v_inventory_id INT; v_stock INT; v_drug_name TEXT; 
+
+BEGIN 
+
+    IF NOT EXISTS (SELECT 1 FROM Patient WHERE patient_id = p_patient_id) 
+
+        THEN RAISE EXCEPTION 'Patient % does not exist.', p_patient_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM Doctor WHERE doctor_id = p_doctor_id) 
+
+        THEN RAISE EXCEPTION 'Doctor % does not exist.', p_doctor_id; 
+
+    END IF; 
+
+    IF NOT EXISTS ( 
+
+        SELECT 1 FROM Patient_diagnosis 
+
+        WHERE patient_diagnosis_id = p_patient_diagnosis_id 
+
+          AND patient_id = p_patient_id 
+
+          AND (date_to IS NULL OR date_to >= CURRENT_DATE) 
+
+    ) THEN 
+
+       RAISE EXCEPTION 'Diagnosis % not active for patient %.', p_patient_diagnosis_id, p_patient_id; 
+
+    END IF; 
+
+    SELECT i.inventory_id, i.quantity, (dp.producer_name | | ' - ' | | ac.atc_code)::TEXT 
+
+    INTO v_inventory_id, v_stock, v_drug_name 
+
+    FROM Inventory i 
+
+    JOIN Drug d ON d.product_id = i.product_id 
+
+    JOIN ATC_code ac ON ac.atc_id = d.atc_id 
+
+    JOIN Drug_producers dp ON dp.drug_prod_id = d.drug_prod_id 
+
+    WHERE d.atc_id = p_atc_id AND i.quantity >= p_dosage 
+
+    ORDER BY i.quantity DESC LIMIT 1;
+
+    IF NOT FOUND THEN 
+
+        RAISE EXCEPTION 'No stock for ATC ID %. Required: %.', p_atc_id, p_dosage; 
+
+    END IF; 
+
+    RETURN QUERY SELECT p_patient_id, p_doctor_id, p_patient_diagnosis_id, p_atc_id, v_inventory_id, p_dosage, p_duration, CURRENT_DATE, v_stock, v_drug_name; 
+
+END; $$ LANGUAGE plpgsql; 
+
+=== Function 3 : fn_check_doctor_availability
+
+The function counts the number of active appointments (excluding CANCELLED and NO_SHOW) that a doctor has on a given date. If the number is less than 30, it returns TRUE (available), otherwise FALSE. The prc_add_appointment_for_referral procedure calls this function and throws an exception if the doctor is not available, instead of directly inserting and then detecting a problem. It can also be used directly from the application to display available appointments.
+
+**Implementation:**
+
+CREATE OR REPLACE FUNCTION fn_check_doctor_availability( 
+
+    p_doctor_id        INT, 
+
+    p_appointment_date DATE 
+
+) 
+
+RETURNS BOOLEAN AS $$ 
+
+DECLARE v_count INT; 
+
+BEGIN 
+
+    IF NOT EXISTS (SELECT 1 FROM Doctor WHERE doctor_id = p_doctor_id) THEN 
+
+        RAISE EXCEPTION 'Doctor with ID % does not exist.', p_doctor_id; 
+
+    END IF; 
+
+    SELECT COUNT(*) INTO v_count 
+
+    FROM Appointment 
+
+    WHERE doctor_id = p_doctor_id AND appointment_date = p_appointment_date AND status NOT IN ('CANCELLED', 'NO_SHOW'); 
+
+    RETURN v_count < 30; 
+
+END; 
+
+$$ LANGUAGE plpgsql; 
+
+=== Procedure 1 : prc_create_referral [Step 1]
+
+The procedure does the following: (1) validates that the patient, department, and referring doctor exist, (2) if a specific doctor is given — validates that it exists and is not the same as the referring doctor, (3) inserts a new record into Referral. This is the entry point into the medical process — without a referral, there is no appointment.
+
+**Implementation:**
+
+CREATE OR REPLACE PROCEDURE prc_create_referral( 
+
+    p_patient_id          INT, 
+
+    p_department_id       INT, 
+
+    p_referring_doctor_id INT, 
+
+    p_referred_doctor_id  INT DEFAULT NULL 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE v_new_referral_id INT; 
+
+BEGIN 
+
+    IF NOT EXISTS (SELECT 1 FROM Patient WHERE patient_id = p_patient_id) THEN 
+
+        RAISE EXCEPTION 'Patient % does not exist.', p_patient_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM Department WHERE department_id = p_department_id) THEN 
+
+        RAISE EXCEPTION 'Department % does not exist.', p_department_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM Doctor WHERE doctor_id = p_referring_doctor_id) THEN 
+
+        RAISE EXCEPTION 'Referring doctor % does not exist.', p_referring_doctor_id; 
+
+    END IF; 
+
+    IF p_referred_doctor_id IS NOT NULL THEN 
+
+        IF NOT EXISTS (SELECT 1 FROM Doctor WHERE doctor_id = p_referred_doctor_id) THEN 
+
+            RAISE EXCEPTION 'Referred doctor % does not exist.', p_referred_doctor_id; 
+
+        END IF; 
+
+        IF p_referred_doctor_id = p_referring_doctor_id THEN 
+
+            RAISE EXCEPTION 'Referring and referred doctor cannot be the same (ID: %).', 
+
+                p_referring_doctor_id; 
+
+        END IF; 
+
+    END IF; 
+
+    SELECT COALESCE(MAX(referral_id),0)+1 INTO v_new_referral_id FROM Referral; 
+
+    INSERT INTO Referral(referral_id,patient_id,department_id, referred_doctor_id,referring_doctor_id) 
+
+    VALUES(v_new_referral_id,p_patient_id,p_department_id, p_referred_doctor_id,p_referring_doctor_id); 
+
+    RAISE NOTICE 'Referral % created. Patient % → Department % by Doctor %.', v_new_referral_id, p_patient_id, p_department_id, p_referring_doctor_id; 
+
+END; $$; 
+
+=== Procedure 2 : prc_add_appointment_for_referral [Step 2]
+
+The procedure does the following: (1) takes the patient_id from the referral, (2) validates the doctor, date (not in the past), type, and priority, (3) calls fn_check_doctor_availability — if the doctor is busy it throws an exception, (4) checks that the referral does not already have an active appointment on that date, (5) inserts a new Appointment with status='SCHEDULED'. This procedure is the second in the flow — a referral must exist first.
+
+**Implemenetation:**
+
+CREATE OR REPLACE PROCEDURE prc_add_appointment_for_referral( 
+
+    p_referral_id INT, p_doctor_id INT, p_appointment_date DATE, 
+
+    p_appointment_type VARCHAR(30) DEFAULT 'REGULAR', 
+
+    p_priority_level   VARCHAR(30) DEFAULT 'MEDIUM' 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE 
+
+    v_patient_id INT; v_new_appt_id INT; v_available BOOLEAN; 
+
+BEGIN 
+
+    SELECT patient_id INTO v_patient_id FROM Referral 
+
+    WHERE referral_id = p_referral_id; 
+
+    IF NOT FOUND THEN 
+
+        RAISE EXCEPTION 'Referral % does not exist.', p_referral_id; 
+ 
+    END IF; 
+
+    IF p_appointment_date < CURRENT_DATE THEN 
+
+        RAISE EXCEPTION 'Date % cannot be in the past.', p_appointment_date; 
+    
+    END IF; 
+
+    SELECT fn_check_doctor_availability(p_doctor_id, p_appointment_date) 
+
+    INTO v_available; 
+
+    IF NOT v_available THEN 
+
+        RAISE EXCEPTION 'Doctor % has no availability on %.', p_doctor_id, p_appointment_date; 
+
+    END IF; 
+
+    IF EXISTS (SELECT 1 FROM Appointment 
+
+               WHERE referral_id=p_referral_id 
+
+                 AND appointment_date=p_appointment_date 
+
+                 AND status NOT IN ('CANCELLED','NO_SHOW')) THEN 
+
+        RAISE EXCEPTION 'Referral % already has an appointment on %.', p_referral_id, p_appointment_date; 
+
+    END IF; 
+
+    SELECT COALESCE(MAX(appointment_id),0)+1 INTO v_new_appt_id FROM Appointment; 
+
+    INSERT INTO Appointment(appointment_id,referral_id,doctor_id,patient_id, prescription_value,appointment_date,status,appointment_type,priority_level) 
+
+    VALUES(v_new_appt_id,p_referral_id,p_doctor_id,v_patient_id, FALSE,p_appointment_date,'SCHEDULED',p_appointment_type,p_priority_level); 
+
+    RAISE NOTICE 'Appointment % for patient % (referral %) with doctor % on %.', v_new_appt_id,v_patient_id,p_referral_id,p_doctor_id,p_appointment_date; 
+
+END; $$;
+
+=== Procedure 3 : prc_complete_examination [Step 3]
+
+The procedure: (1) takes the appointment details, (2) validates that it is not already completed, cancelled, or in the future, (3) checks that there is no other examination for that appointment, (4) sets status='COMPLETED' — this automatically triggers trg_appointment_status_audit, (5)
+inserts a new record into Medical_examination. After completing this procedure, the doctor can
+add a diagnosis.
+
+**Implementation:**
+
+CREATE OR REPLACE PROCEDURE prc_complete_examination( 
+
+    p_appointment_id INT, 
+
+    p_notes TEXT DEFAULT 'Examination completed.' 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE 
+
+    v_doctor_id INT; v_exam_date DATE; 
+
+    v_new_exam_id INT; v_current_status VARCHAR(30); 
+
+BEGIN 
+
+    SELECT doctor_id, appointment_date, status 
+
+    INTO v_doctor_id, v_exam_date, v_current_status 
+
+    FROM Appointment WHERE appointment_id = p_appointment_id; 
+
+    IF NOT FOUND THEN 
+
+        RAISE EXCEPTION 'Appointment % does not exist.', p_appointment_id; 
+
+    END IF; 
+
+    IF v_current_status = 'COMPLETED' THEN 
+
+        RAISE EXCEPTION 'Appointment % already completed.', p_appointment_id; 
+
+    END IF; 
+
+    IF v_current_status = 'CANCELLED' THEN 
+
+        RAISE EXCEPTION 'Cannot complete CANCELLED appointment %.', p_appointment_id; 
+
+    END IF; 
+
+    IF v_exam_date > CURRENT_DATE THEN 
+
+        RAISE EXCEPTION 'Cannot complete future appointment % on %.', p_appointment_id, v_exam_date; 
+
+    END IF; 
+
+    IF EXISTS (SELECT 1 FROM Medical_examination WHERE appointment_id=p_appointment_id) THEN 
+
+        RAISE EXCEPTION 'Exam already exists for appointment %.', p_appointment_id; 
+
+    END IF; 
+
+    UPDATE Appointment SET status='COMPLETED' WHERE appointment_id=p_appointment_id; 
+
+    SELECT COALESCE(MAX(exam_id),0)+1 INTO v_new_exam_id FROM Medical_examination; 
+
+    INSERT INTO Medical_examination(exam_id,exam_date,notes,doctor_id,appointment_id) 
+
+    VALUES(v_new_exam_id,v_exam_date,p_notes,v_doctor_id,p_appointment_id); 
+
+    RAISE NOTICE 'Appointment % completed. Examination % created.', p_appointment_id, v_new_exam_id; 
+
+END; $$;
+
+=== Procedure 4 : prc_add_new_diagnosis [Step 4]
+
+The procedure: (1) takes the patient_id and the exam date via exam_id, (2) validates that the doctor and ICD code exist, (3) validates that date_to (if specified) is not before the exam date, (4) if the diagnosis is set as primary — reports if the patient already has an active primary, (5) inserts a new record into Patient_diagnosis. This procedure is a prerequisite for prc_create_prescription.
+
+**Implementation:**
+
+CREATE OR REPLACE PROCEDURE prc_add_new_diagnosis( 
+
+    p_exam_id        INT, 
+
+    p_icd_id         INT, 
+
+    p_doctor_id      INT, 
+
+    p_diagnosis_name VARCHAR(255), 
+
+    p_is_primary     BOOLEAN DEFAULT FALSE, 
+
+    p_date_to        DATE    DEFAULT NULL 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE 
+
+    v_patient_id INT; v_exam_date DATE; v_new_diagnosis_id INT; 
+
+BEGIN 
+
+    SELECT a.patient_id, me.exam_date INTO v_patient_id, v_exam_date 
+
+    FROM Medical_examination me 
+
+    JOIN Appointment a ON a.appointment_id = me.appointment_id 
+
+    WHERE me.exam_id = p_exam_id; 
+
+    IF NOT FOUND THEN 
+
+        RAISE EXCEPTION 'Exam % does not exist.', p_exam_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM Doctor WHERE doctor_id = p_doctor_id) THEN 
+
+        RAISE EXCEPTION 'Doctor % does not exist.', p_doctor_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM ICD WHERE icd_id = p_icd_id) THEN 
+
+        RAISE EXCEPTION 'ICD code % does not exist.', p_icd_id; 
+
+    END IF; 
+
+    IF p_date_to IS NOT NULL AND p_date_to < v_exam_date THEN 
+
+        RAISE EXCEPTION 'date_to (%) cannot be before exam date (%).', p_date_to, v_exam_date; 
+
+    END IF; 
+
+    SELECT COALESCE(MAX(patient_diagnosis_id),0)+1 
+
+    INTO v_new_diagnosis_id FROM Patient_diagnosis; 
+
+    INSERT INTO Patient_diagnosis(patient_diagnosis_id,patient_id,doctor_id, exam_id,icd_id,diagnosis_name,is_primary,date_from,date_to) 
+
+    VALUES(v_new_diagnosis_id,v_patient_id,p_doctor_id, p_exam_id,p_icd_id,p_diagnosis_name,p_is_primary,v_exam_date,p_date_to); 
+
+    RAISE NOTICE 'Diagnosis % (ID: %) added for patient % from exam %.', p_diagnosis_name, v_new_diagnosis_id, v_patient_id, p_exam_id; 
+
+END; $$; 
+
+=== Procedure 5 : prc_create_prescription [Step 5]
+
+The procedure calls fn_prescribe_medicine which performs all the validation (patient, doctor, diagnosis, ATC, stock). If the function succeeds, the procedure additionally checks that there is no existing prescription for that diagnosis and ATC code, and inserts a new entry into Prescription. This procedure is a prerequisite for prc_buy_prescription.
+
+**Implementation:**
+
+CREATE OR REPLACE PROCEDURE prc_create_prescription( 
+
+    p_patient_id           INT, 
+
+    p_doctor_id            INT, 
+
+    p_patient_diagnosis_id INT, 
+
+    p_atc_id               INT, 
+
+    p_dosage               INT, 
+
+    p_duration             INT 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE 
+
+    v_new_presc_id INT; v_inventory_id INT; 
+
+    v_drug_name TEXT; v_stock INT; 
+
+BEGIN 
+
+    SELECT r.inventory_id, r.available_stock, r.drug_name 
+
+    INTO v_inventory_id, v_stock, v_drug_name 
+
+    FROM fn_prescribe_medicine(p_patient_id, p_doctor_id, p_patient_diagnosis_id, p_atc_id, p_dosage, p_duration) r; 
+
+    IF EXISTS (SELECT 1 FROM Prescription 
+
+               WHERE patient_diagnosis_id = p_patient_diagnosis_id AND atc_id = p_atc_id AND patient_id = p_patient_id) THEN 
+
+        RAISE EXCEPTION 'Prescription for ATC % already exists for diagnosis %.', p_atc_id, p_patient_diagnosis_id; 
+
+    END IF; 
+
+    SELECT COALESCE(MAX(presc_id),0)+1 INTO v_new_presc_id FROM Prescription; 
+
+    INSERT INTO Prescription(presc_id,atc_id,doctor_id,patient_id, presc_date,duration,dosage,inventory_id,patient_diagnosis_id) 
+
+    VALUES(v_new_presc_id,p_atc_id,p_doctor_id,p_patient_id, CURRENT_DATE,p_duration,p_dosage,v_inventory_id,p_patient_diagnosis_id); 
+
+    RAISE NOTICE 'Prescription % created. Drug: %. Dosage: %. Duration: % days.', v_new_presc_id, v_drug_name, p_dosage, p_duration; 
+
+END; $$; 
+
+=== Procedure 6 : prc_buy_prescription [Step 6]
+
+The procedure: (1) takes the prescription details, (2) validates that the pharmacy exists, (3) checks that the prescription is not already filled at that pharmacy, (4) checks inventory, (5) takes the last sale price, (6) inserts Pharmacy_sale, (7) inserts Sale_item, and (8) decrements the quantity in Inventory. This is the final step in the entire medical process and closes the referral → medication cycle.
+
+**Implementation:**
+
+CREATE OR REPLACE PROCEDURE prc_buy_prescription( 
+
+    p_presc_id INT, p_pharmacy_id INT 
+
+) 
+
+LANGUAGE plpgsql AS $$ 
+
+DECLARE 
+
+    v_patient_id INT; v_inventory_id INT; v_dosage INT; 
+
+    v_current_qty INT; v_sale_price NUMERIC; 
+
+    v_total_amount NUMERIC; v_new_sale_id INT; v_new_item_id INT; 
+
+BEGIN 
+
+    SELECT patient_id, inventory_id, dosage INTO v_patient_id, v_inventory_id, v_dosage 
+
+    FROM Prescription WHERE presc_id = p_presc_id; 
+
+    IF NOT FOUND THEN 
+
+        RAISE EXCEPTION 'Prescription % does not exist.', p_presc_id; 
+
+    END IF; 
+
+    IF NOT EXISTS (SELECT 1 FROM Pharmacy WHERE pharmacy_id = p_pharmacy_id) THEN 
+
+        RAISE EXCEPTION 'Pharmacy % does not exist.', p_pharmacy_id; 
+
+    END IF; 
+
+    IF EXISTS (SELECT 1 FROM Pharmacy_sale WHERE presc_id=p_presc_id AND pharmacy_id=p_pharmacy_id) THEN 
+
+        RAISE EXCEPTION 'Prescription % already dispensed at pharmacy %.', p_presc_id, p_pharmacy_id; 
+
+    END IF; 
+
+    SELECT quantity INTO v_current_qty FROM Inventory WHERE inventory_id=v_inventory_id; 
+
+    IF v_current_qty < v_dosage THEN 
+
+        RAISE EXCEPTION 'Insufficient stock. Required: %, Available: %.', v_dosage, v_current_qty; 
+
+    END IF; 
+
+    SELECT sale_price INTO v_sale_price FROM Inventory_price 
+
+    WHERE inventory_id=v_inventory_id ORDER BY date DESC LIMIT 1; 
+
+    v_total_amount := ROUND(v_sale_price * v_dosage, 2); 
+
+    SELECT COALESCE(MAX(sale_id),0)+1 INTO v_new_sale_id FROM Pharmacy_sale; 
+
+    INSERT INTO Pharmacy_sale(sale_id,patient_id,pharmacy_id,presc_id,sale_date,total_amount) 
+
+    VALUES(v_new_sale_id,v_patient_id,p_pharmacy_id,p_presc_id,CURRENT_DATE,v_total_amount); 
+
+    SELECT COALESCE(MAX(sale_item_id),0)+1 INTO v_new_item_id FROM Sale_item; 
+
+    INSERT INTO Sale_item(sale_item_id,sale_id,presc_id,inventory_id,date) 
+
+    VALUES(v_new_item_id,v_new_sale_id,p_presc_id,v_inventory_id,CURRENT_DATE); 
+
+    UPDATE Inventory SET quantity=quantity-v_dosage WHERE inventory_id=v_inventory_id; 
+
+    RAISE NOTICE 'Prescription % dispensed at pharmacy %. Sale: %. Total: %. Stock left: %.', p_presc_id,p_pharmacy_id,v_new_sale_id,v_total_amount,v_current_qtyv_dosage; 
+
+END; $$;