Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

06/08/26 10:26:59 (10 days ago)

Author:

231062

Comment:

--

DatabaseProgramming

@@ -350 +350 @@
         WHERE resource_edition_isbn = p_isbn
     );
+END;
+$$;
+}}}
+
+Leave review
+{{{
+CREATE OR REPLACE FUNCTION fn_leave_review(
+    p_user_id BIGINT,
+    p_edition_isbn VARCHAR(20),
+    p_rating INT,
+    p_review_text TEXT
+)
+RETURNS BIGINT
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_review_id BIGINT;
+BEGIN
+    IF p_rating NOT BETWEEN 1 AND 5 THEN
+        RAISE EXCEPTION 'Rating must be between 1 and 5';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM Rental r
+        JOIN Membership m
+            ON m.id = r.membership_id
+        JOIN ResourceCopy rc
+            ON rc.id = r.resource_copy_id
+        WHERE m.user_id = p_user_id
+          AND rc.edition_isbn = p_edition_isbn
+          AND r.returned_at IS NOT NULL
+    ) THEN
+        RAISE EXCEPTION
+            'User has not completed a rental of this edition';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM Review
+        WHERE user_id = p_user_id
+          AND resource_edition_isbn = p_edition_isbn
+    ) THEN
+        RAISE EXCEPTION
+            'User has already reviewed this edition';
+    END IF;
+
+    INSERT INTO Review (
+        user_id,
+        resource_edition_isbn,
+        rating,
+        review_text,
+        created_at,
+        updated_at
+    )
+    VALUES (
+        p_user_id,
+        p_edition_isbn,
+        p_rating,
+        p_review_text,
+        NOW(),
+        NOW()
+    )
+    RETURNING id INTO v_review_id;
+
+    RETURN v_review_id;
 END;
 $$;