Changes between Version 2 and Version 3 of Stored Functions and Procedures

Timestamp:

05/12/26 02:31:58 (2 weeks ago)

Author:

193284

Comment:

--

Stored Functions and Procedures

@@ -11 +11 @@
 Calculates the total cost of a wedding using venue, photographer, and band bookings.
 
+=== SQL Code ===
+
+<pre>
+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;
+</pre>
+
 == 2. is_venue_available() ==
 
@@ -17 +37 @@
 Checks whether a venue is available for a selected date and time interval.
 
+=== SQL Code ===
+
+<pre>
+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;
+</pre>
+
 == 3. is_photographer_available() ==
 
@@ -22 +67 @@
 
 Checks whether a photographer is available for a selected date and time interval.
+
+=== SQL Code ===
+
+<pre>
+CREATE OR REPLACE FUNCTION is_photographer_available(
+    p_photographer_id INTEGER,
+    p_date DATE,
+    p_start TIME,
+    p_end TIME
+)
+RETURNS BOOLEAN AS $$
+BEGIN
+    RETURN NOT EXISTS (
+        SELECT 1
+        FROM photographer_booking
+        WHERE photographer_id = p_photographer_id
+          AND "date" = p_date
+          AND (
+                p_start < end_time
+            AND p_end > start_time
+          )
+    );
+END;
+$$ LANGUAGE plpgsql;
+</pre>
 
 == 4. generate_rsvp_summary() ==
@@ -31 +101 @@
 === SQL Code ===
 
-{{collapse(View SQL Code)
-<syntaxhighlight lang="sql">
-PASTE SQL FROM advanced_functions.sql
-</syntaxhighlight>
-}}
+<pre>
+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;
+$$;
+</pre>