Changes between Version 9 and Version 10 of DatabaseProgramming

Timestamp:

06/14/26 15:52:55 (3 days ago)

Author:

231072

Comment:

--

DatabaseProgramming

@@ -77 +77 @@
 
 
+= Процедура add_product
 
+Додава нов продукт во системот заедно со неговите атрибути, статистики и слики.
+
+{{{
+CREATE OR REPLACE PROCEDURE add_product(
+    p_title VARCHAR,
+    p_description VARCHAR,
+    p_price NUMERIC,
+    p_location VARCHAR,
+    p_currency VARCHAR,
+    p_category_id INT,
+    p_seller_id INT,
+    p_quantity INT,
+    p_attributes JSONB,   -- ex. '[{"category_attribute_id": 11, "attribute_value": "M"}, ...]'
+    p_images TEXT[]       -- ex. ARRAY['url1.jpg', 'url2.jpg']
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_product_id INT;
+    v_attr JSONB;
+    v_image TEXT;
+begin
+    -- Validacii
+    IF p_price <= 0 THEN
+        RAISE EXCEPTION 'Cenata mora da e pogolema od 0';
+    END IF;
+
+    IF p_quantity < 0 THEN
+        RAISE EXCEPTION 'Kolichinata ne moze da e negativna';
+    END IF;
+
+   
+    IF EXISTS (
+        SELECT 1 FROM category WHERE parent_category = p_category_id
+    ) THEN
+        RAISE EXCEPTION 'Kategorijata mora da e leaf kategorija';
+    END IF;
+
+    
+    --FUNKCIJA KORISNIK
+   perform fn_validate_seller_package(p_seller_id);
+   
+
+    -- Insert produkt
+    INSERT INTO product (title, description, price, "location" , category_id, seller_id, created_at, is_active, currency, quantity)
+        VALUES (
+            p_title,
+            p_description,
+            p_price,
+            p_location,
+            p_category_id,
+            p_seller_id,
+            CURRENT_TIMESTAMP,
+            CASE WHEN p_quantity > 0 THEN TRUE ELSE FALSE END,
+            p_currency,
+            p_quantity
+        )
+        RETURNING product_id INTO v_product_id;
+
+    -- Insert productstats (pochetni vrednosti)
+    INSERT INTO productstats (product_id, views_count, favorites_count, sells_count)
+    VALUES (v_product_id, 0, 0, 0);
+
+    -- Insert atributi funkcija
+   IF p_attributes IS NOT NULL then
+           FOR v_attr IN SELECT value FROM jsonb_array_elements(p_attributes) LOOP
+            PERFORM fn_validate_product_attribute(
+                p_category_id,
+                (v_attr->>'category_attribute_id')::INT,
+                v_attr->>'attribute_value'
+            );
+            INSERT INTO productattributes (product_id, category_attribute_id, attribute_value)
+            VALUES (v_product_id, (v_attr->>'category_attribute_id')::INT, v_attr->>'attribute_value');
+                END LOOP;
+        END IF;
+
+    -- Insert sliki
+    IF p_images IS NOT NULL THEN
+        FOREACH v_image IN ARRAY p_images
+        LOOP
+            INSERT INTO productimages (product_id, image_url)
+            VALUES (v_product_id, v_image);
+        END LOOP;
+    END IF;
+
+    RAISE NOTICE 'Produktot e dodaden so ID: %', v_product_id;
+end;
+$$;
+}}}