Changes between Version 7 and Version 8 of DatabaseProgramming

Timestamp:

06/11/26 10:29:29 (5 days ago)

Author:

231035

Comment:

--

DatabaseProgramming

@@ -99 +99 @@
 == A review can be made only for a complete booking
 {{{
-create or replace procedure review_booking(r_booking_id bigint,rating int,comment varchar)
-as $$
-    declare
-            v_status varchar;
-        guest_id bigint;
-        property bigint;
-    begin
-        select b.booking_status,b.guest_id,r.property_id
-        into v_status,guest_id,property
-        from bookings b
-        join rooms r on b.room_id = r.room_id
-        where booking_id = r_booking_id;
-
-
-
-        if v_status <> 'COMPLETED' then
-            raise exception  'Can only review completed bookings';
-        end if;
-        insert into reviews(booking_id, guest_id, property_id, rating, comment) values
-                                                                                    (r_booking_id,guest_id,
-                                                                                    property, review_booking.rating,review_booking.comment);
-
-    end;
-    $$ language plpgsql;
+CREATE OR REPLACE PROCEDURE review_booking(
+    p_booking_id BIGINT,
+    p_rating INT,
+    p_comment VARCHAR
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_status VARCHAR;
+    v_guest_id BIGINT;
+    v_property_id BIGINT;
+BEGIN
+    SELECT 
+        b.booking_status,
+        b.guest_id,
+        r.property_id
+    INTO 
+        v_status,
+        v_guest_id,
+        v_property_id
+    FROM bookings b
+    JOIN rooms r ON b.room_id = r.room_id
+    WHERE b.booking_id = p_booking_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Booking with id % does not exist.', p_booking_id;
+    END IF;
+
+    IF v_status <> 'COMPLETED' THEN
+        RAISE EXCEPTION 'Can only review completed bookings.';
+    END IF;
+
+    IF p_rating < 1 OR p_rating > 5 THEN
+        RAISE EXCEPTION 'Rating must be between 1 and 5.';
+    END IF;
+
+    INSERT INTO reviews (
+        booking_id,
+        guest_id,
+        property_id,
+        rating,
+        comment
+    )
+    VALUES (
+        p_booking_id,
+        v_guest_id,
+        v_property_id,
+        p_rating,
+        p_comment
+    );
+END;
+$$;
 }}}