pg_insert_product.sql (5062B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 18 DROP FUNCTION IF EXISTS merchant_do_insert_product; 19 CREATE FUNCTION merchant_do_insert_product ( 20 IN in_instance_id TEXT, 21 IN in_product_id TEXT, 22 IN in_description TEXT, 23 IN in_description_i18n JSONB, 24 IN in_unit TEXT, 25 IN in_image TEXT, 26 IN in_taxes JSONB, 27 IN in_price taler_amount_currency, 28 IN ina_price_list taler_amount_currency[], 29 IN in_total_stock INT8, 30 IN in_total_stock_frac INT4, 31 IN in_allow_fractional_quantity BOOL, 32 IN in_fractional_precision_level INT4, 33 IN in_address JSONB, 34 IN in_next_restock INT8, 35 IN in_minimum_age INT4, 36 IN ina_categories INT8[], 37 IN in_product_name TEXT, 38 OUT out_no_instance BOOL, 39 OUT out_conflict BOOL, 40 OUT out_no_cat INT8) 41 LANGUAGE plpgsql 42 AS $$ 43 DECLARE 44 my_merchant_id INT8; 45 my_product_serial INT8; 46 i INT8; 47 ini_cat INT8; 48 my_price taler_amount_currency; 49 my_price_array taler_amount_currency[]; 50 BEGIN 51 52 -- Which instance are we using? 53 SELECT merchant_serial 54 INTO my_merchant_id 55 FROM merchant_instances 56 WHERE merchant_id=in_instance_id; 57 58 IF NOT FOUND 59 THEN 60 out_no_instance=TRUE; 61 out_conflict=FALSE; 62 out_no_cat=NULL; 63 RETURN; 64 END IF; 65 out_no_instance=FALSE; 66 67 IF COALESCE (array_length(ina_price_list,1),0) = 0 68 THEN 69 my_price_array := ARRAY[in_price]::taler_amount_currency[]; 70 ELSE 71 my_price_array := ina_price_list; 72 END IF; 73 my_price := my_price_array[1]; 74 75 INSERT INTO merchant_inventory 76 (merchant_serial 77 ,product_id 78 ,product_name 79 ,description 80 ,description_i18n 81 ,unit 82 ,image 83 ,image_hash 84 ,taxes 85 ,price 86 ,price_array 87 ,total_stock 88 ,total_stock_frac 89 ,allow_fractional_quantity 90 ,fractional_precision_level 91 ,address 92 ,next_restock 93 ,minimum_age 94 ) VALUES ( 95 my_merchant_id 96 ,in_product_id 97 ,in_product_name 98 ,in_description 99 ,in_description_i18n 100 ,in_unit 101 ,in_image 102 ,CASE 103 WHEN (in_image IS NULL) OR (in_image = '') 104 THEN NULL 105 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 106 'sha256'), 107 'hex') 108 END 109 ,in_taxes 110 ,my_price 111 ,my_price_array 112 ,in_total_stock 113 ,in_total_stock_frac 114 ,in_allow_fractional_quantity 115 ,in_fractional_precision_level 116 ,in_address 117 ,in_next_restock 118 ,in_minimum_age) 119 ON CONFLICT (merchant_serial, product_id) DO NOTHING 120 RETURNING product_serial 121 INTO my_product_serial; 122 123 124 IF NOT FOUND 125 THEN 126 -- Check for idempotency 127 SELECT product_serial 128 INTO my_product_serial 129 FROM merchant_inventory 130 WHERE merchant_serial=my_merchant_id 131 AND product_id=in_product_id 132 AND product_name=in_product_name 133 AND description=in_description 134 AND description_i18n=in_description_i18n 135 AND unit=in_unit 136 AND image=in_image 137 AND taxes=in_taxes 138 AND price=my_price 139 AND to_jsonb(COALESCE(price_array, ARRAY[]::taler_amount_currency[])) 140 = to_jsonb(COALESCE(my_price_array, ARRAY[]::taler_amount_currency[])) 141 AND total_stock=in_total_stock 142 AND total_stock_frac=in_total_stock_frac 143 AND allow_fractional_quantity=in_allow_fractional_quantity 144 AND fractional_precision_level=in_fractional_precision_level 145 AND address=in_address 146 AND next_restock=in_next_restock 147 AND minimum_age=in_minimum_age; 148 IF NOT FOUND 149 THEN 150 out_conflict=TRUE; 151 out_no_cat=NULL; 152 RETURN; 153 END IF; 154 155 -- Check categories match as well 156 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 157 LOOP 158 ini_cat=ina_categories[i]; 159 160 PERFORM 161 FROM merchant_product_categories 162 WHERE product_serial=my_product_serial 163 AND category_serial=ini_cat; 164 IF NOT FOUND 165 THEN 166 out_conflict=TRUE; 167 out_no_cat=NULL; 168 RETURN; 169 END IF; 170 END LOOP; 171 172 -- Also check there are no additional categories 173 -- in either set. 174 SELECT COUNT(*) 175 INTO i 176 FROM merchant_product_categories 177 WHERE product_serial=my_product_serial; 178 IF i != array_length(ina_categories,1) 179 THEN 180 out_conflict=TRUE; 181 out_no_cat=NULL; 182 RETURN; 183 END IF; 184 185 -- Is idempotent! 186 out_conflict=FALSE; 187 out_no_cat=NULL; 188 RETURN; 189 END IF; 190 out_conflict=FALSE; 191 192 193 -- Add categories 194 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 195 LOOP 196 ini_cat=ina_categories[i]; 197 198 INSERT INTO merchant_product_categories 199 (product_serial 200 ,category_serial) 201 VALUES 202 (my_product_serial 203 ,ini_cat) 204 ON CONFLICT DO NOTHING; 205 206 IF NOT FOUND 207 THEN 208 out_no_cat=i; 209 RETURN; 210 END IF; 211 END LOOP; 212 213 -- Success! 214 out_no_cat=NULL; 215 END $$;