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

Timestamp:

05/13/26 17:49:04 (13 days ago)

Author:

193284

Comment:

--

Stored Functions and Procedures

@@ -93 +93 @@
 </pre>
 
-== 4. generate_rsvp_summary() ==
+== 4. is_band_available() ==
+
+=== Description ===
+
+Checks whether a band is available for a selected date and time interval.
+
+This function uses full timestamp interval comparison by combining the booking date with start and end time. This avoids incorrect availability results when intervals overlap in different ways.
+
+=== SQL Code ===
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION is_band_available(
+    p_band_id INTEGER,
+    p_date DATE,
+    p_start TIME,
+    p_end TIME
+)
+RETURNS BOOLEAN AS $$
+BEGIN
+    IF p_end <= p_start THEN
+        RAISE EXCEPTION 'Invalid interval: end time must be after start time.';
+    END IF;
+
+    RETURN NOT EXISTS (
+        SELECT 1
+        FROM band_booking bb
+        WHERE bb.band_id = p_band_id
+          AND bb.status <> 'cancelled'
+          AND (
+                (p_date + p_start) < (bb."date" + bb.end_time)
+            AND (p_date + p_end)   > (bb."date" + bb.start_time)
+          )
+    );
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+== 5. generate_rsvp_summary() ==
 
 === Description ===