Changes between Version 3 and Version 4 of DatabaseProgramming

Timestamp:

05/12/26 15:49:07 (2 weeks ago)

Author:

231035

Comment:

--

DatabaseProgramming

@@ -218 +218 @@
     $$;
 }}}
+
+== Create a booking
+{{{
+CREATE OR REPLACE PROCEDURE book_room(
+    p_room_id        BIGINT,
+    p_guest_id       BIGINT,
+    p_check_in_date  DATE,
+    p_check_out_date DATE,
+    p_guests_count   INT,
+    p_total_price    NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF p_check_out_date <= p_check_in_date THEN
+        RAISE EXCEPTION 'Check-out date must be after check-in date';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM bookings b
+        WHERE b.room_id = p_room_id
+          AND b.booking_status IN ('PENDING', 'CONFIRMED')
+          AND daterange(b.check_in_date, b.check_out_date, '[)')
+              && daterange(p_check_in_date, p_check_out_date, '[)')
+    ) THEN
+        RAISE EXCEPTION 'Room % is already booked for selected dates', p_room_id;
+    END IF;
+
+    INSERT INTO bookings (
+        room_id,
+        guest_id,
+        check_in_date,
+        check_out_date,
+        guests_count,
+        total_price,
+        booking_status
+    )
+    VALUES (
+        p_room_id,
+        p_guest_id,
+        p_check_in_date,
+        p_check_out_date,
+        p_guests_count,
+        p_total_price,
+        'PENDING'
+    );
+
+    RAISE NOTICE 'Booking created successfully';
+END;
+$$;
+}}}