Changes between Initial Version and Version 1 of Stored Functions and Procedures

Timestamp:

05/12/26 01:36:59 (2 weeks ago)

Author:

193284

Comment:

--

Stored Functions and Procedures

@@ +1 @@
+= Stored Functions and Procedures =
+
+== Description ==
+
+This section contains reusable SQL functions and procedures used for calculations, validation, and reporting.
+
+== 1. calculate_wedding_total_cost() ==
+
+=== Description ===
+
+Calculates the total cost of a wedding using venue, photographer, and band bookings.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER)
+RETURNS NUMERIC AS $$
+DECLARE
+    total_cost NUMERIC;
+BEGIN
+    SELECT
+        COALESCE(SUM(vb.price), 0)
+    INTO total_cost
+    FROM wedding w
+    LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
+    WHERE w.wedding_id = p_wedding_id;
+
+    RETURN total_cost;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+== 2. is_venue_available() ==
+
+=== Description ===
+
+Checks whether a venue is available for a selected date and time interval.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE FUNCTION is_venue_available(
+    p_venue_id INTEGER,
+    p_date DATE,
+    p_start TIME,
+    p_end TIME
+)
+RETURNS BOOLEAN AS $$
+BEGIN
+    RETURN NOT EXISTS (
+        SELECT 1
+        FROM venue_booking
+        WHERE venue_id = p_venue_id
+          AND "date" = p_date
+          AND (
+                p_start < end_time
+            AND p_end > start_time
+          )
+    );
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+== 3. generate_rsvp_summary() ==
+
+=== Description ===
+
+Returns RSVP statistics for a selected event.
+
+=== SQL Code ===
+
+{{{
+CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RAISE NOTICE 'Accepted: %',
+    (
+        SELECT COUNT(*)
+        FROM event_rsvp
+        WHERE event_id = p_event_id
+          AND status = 'accepted'
+    );
+END;
+$$;
+}}}