Changes between Version 9 and Version 10 of AdvancedDatabaseDevelopment

Timestamp:

07/01/26 18:20:36 (6 days ago)

Author:

232012

Comment:

--

AdvancedDatabaseDevelopment

@@ -9 +9 @@
 }}}
 * **Implementation:**  
+
+{{{
+CREATE OR REPLACE PROCEDURE project.register_new_consumer_with_gift(
+    p_email VARCHAR,
+    p_username VARCHAR,
+    p_password VARCHAR,
+    p_shipping_address VARCHAR,
+    p_telephone_number VARCHAR
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    new_user_id BIGINT;
+    new_wishlist_id BIGINT;
+BEGIN
+
+    IF EXISTS (
+        SELECT 1
+        FROM project.users
+        WHERE email = p_email
+           OR username = p_username
+           OR telephone_number = p_telephone_number
+    ) THEN
+        RAISE EXCEPTION
+            'A user with the same email, username, or telephone number already exists.';
+    END IF;
+
+    SELECT COALESCE(MAX(user_id), 0) + 1
+    INTO new_user_id
+    FROM project.users;
+
+    SELECT COALESCE(MAX(wishlist_id), 0) + 1
+    INTO new_wishlist_id
+    FROM project.wishlists;
+
+    INSERT INTO project.users
+    (
+        user_id,
+        email,
+        username,
+        password,
+        date_created,
+        shipping_address,
+        telephone_number
+    )
+    VALUES
+    (
+        new_user_id,
+        p_email,
+        p_username,
+        p_password,
+        CURRENT_DATE,
+        p_shipping_address,
+        p_telephone_number
+    );
+
+    INSERT INTO project.consumers
+    (
+        user_id,
+        points_collected
+    )
+    VALUES
+    (
+        new_user_id,
+        50
+    );
+
+    INSERT INTO project.wishlists
+    (
+        wishlist_id,
+        user_id
+    )
+    VALUES
+    (
+        new_wishlist_id,
+        new_user_id
+    );
+
+END;
+$$;
+}}}
 
 ==== Address and Phone Number Verifier