insert_product.sql (6496B)
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 DROP FUNCTION IF EXISTS merchant_do_insert_product; 18 CREATE FUNCTION merchant_do_insert_product ( 19 IN in_product_id TEXT, 20 IN in_description TEXT, 21 IN in_description_i18n JSONB, -- $3 22 IN in_unit TEXT, 23 IN in_image TEXT, 24 IN in_taxes JSONB, -- $6 25 IN ina_price_list merchant.taler_amount_currency[], 26 IN in_total_stock INT8, -- $8 27 IN in_total_stock_frac INT4, --$9 28 IN in_allow_fractional_quantity BOOL, 29 IN in_fractional_precision_level INT4, 30 IN in_address JSONB, -- $12 31 IN in_next_restock INT8, 32 IN in_minimum_age INT4, 33 IN ina_categories INT8[], -- $15 34 IN in_product_name TEXT, 35 IN in_product_group_id INT8, -- NULL for default 36 IN in_money_pot_id INT8, -- NULL for none 37 IN in_price_is_net BOOL, -- $19 38 OUT out_conflict BOOL, 39 OUT out_no_cat INT8, 40 OUT out_no_group BOOL, 41 OUT out_no_pot BOOL, 42 OUT out_no_unit BOOL) 43 LANGUAGE plpgsql 44 AS $$ 45 DECLARE 46 my_product_serial INT8; 47 i INT8; 48 ini_cat INT8; 49 BEGIN 50 51 out_no_group = FALSE; 52 out_no_pot = FALSE; 53 out_no_unit = FALSE; 54 55 -- Check the unit exists. 'unit' is a slug, but nothing tied it to the 56 -- unit tables, so a product could name a unit that GET /private/units 57 -- does not know. Checked first, before anything is modified. 58 -- NOTE: a *repeated* POST of a product that was stored with an unknown 59 -- unit by an older backend now reports the unknown unit instead of 60 -- replying 204 idempotent. That is the point: the unit has to be 61 -- created before the product can be re-sent. 62 PERFORM FROM merchant_custom_units 63 WHERE unit=in_unit; 64 IF NOT FOUND 65 THEN 66 PERFORM FROM merchant.merchant_builtin_units 67 WHERE unit=in_unit; 68 IF NOT FOUND 69 THEN 70 out_no_unit=TRUE; 71 out_conflict=FALSE; 72 out_no_cat=NULL; 73 RETURN; 74 END IF; 75 END IF; 76 77 IF in_product_group_id IS NOT NULL 78 THEN 79 PERFORM FROM merchant_product_groups 80 WHERE product_group_serial=in_product_group_id; 81 IF NOT FOUND 82 THEN 83 out_no_group=TRUE; 84 out_conflict=FALSE; 85 out_no_cat=NULL; 86 RETURN; 87 END IF; 88 END IF; 89 90 IF in_money_pot_id IS NOT NULL 91 THEN 92 PERFORM FROM merchant_money_pots 93 WHERE money_pot_serial=in_money_pot_id; 94 IF NOT FOUND 95 THEN 96 out_no_pot=TRUE; 97 out_conflict=FALSE; 98 out_no_cat=NULL; 99 RETURN; 100 END IF; 101 END IF; 102 103 -- Check all categories exist. Must happen before we modify anything, 104 -- as we do not roll back on the out_no_cat return path. 105 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 106 LOOP 107 ini_cat=ina_categories[i]; 108 109 PERFORM 110 FROM merchant_categories 111 WHERE category_serial=ini_cat; 112 IF NOT FOUND 113 THEN 114 out_no_cat=i; 115 out_conflict=FALSE; 116 RETURN; 117 END IF; 118 END LOOP; 119 120 INSERT INTO merchant_inventory 121 (product_id 122 ,product_name 123 ,description 124 ,description_i18n 125 ,unit 126 ,image 127 ,image_hash 128 ,taxes 129 ,price_array 130 ,total_stock 131 ,total_stock_frac 132 ,allow_fractional_quantity 133 ,fractional_precision_level 134 ,address 135 ,next_restock 136 ,minimum_age 137 ,product_group_serial 138 ,money_pot_serial 139 ,price_is_net 140 ) VALUES ( 141 in_product_id 142 ,in_product_name 143 ,in_description 144 ,in_description_i18n 145 ,in_unit 146 ,in_image 147 ,CASE 148 WHEN (in_image IS NULL) OR (in_image = '') 149 THEN NULL 150 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 151 'sha256'), 152 'hex') 153 END 154 ,in_taxes 155 ,ina_price_list 156 ,in_total_stock 157 ,in_total_stock_frac 158 ,in_allow_fractional_quantity 159 ,in_fractional_precision_level 160 ,in_address 161 ,in_next_restock 162 ,in_minimum_age 163 ,in_product_group_id 164 ,in_money_pot_id 165 ,in_price_is_net 166 ) 167 ON CONFLICT (product_id) DO NOTHING 168 RETURNING product_serial 169 INTO my_product_serial; 170 171 172 IF NOT FOUND 173 THEN 174 -- Check for idempotency 175 SELECT product_serial 176 INTO my_product_serial 177 FROM merchant_inventory 178 WHERE product_id=in_product_id 179 AND product_name=in_product_name 180 AND description=in_description 181 AND description_i18n=in_description_i18n 182 AND unit=in_unit 183 AND image=in_image 184 AND taxes=in_taxes 185 AND to_jsonb(COALESCE(price_array, ARRAY[]::merchant.taler_amount_currency[])) 186 = to_jsonb(COALESCE(ina_price_list, ARRAY[]::merchant.taler_amount_currency[])) -- FIXME: wild. Why so complicated? 187 AND total_stock=in_total_stock 188 AND total_stock_frac=in_total_stock_frac 189 AND allow_fractional_quantity=in_allow_fractional_quantity 190 AND fractional_precision_level=in_fractional_precision_level 191 AND address=in_address 192 AND next_restock=in_next_restock 193 AND minimum_age=in_minimum_age 194 AND product_group_serial IS NOT DISTINCT FROM in_product_group_id 195 AND money_pot_serial IS NOT DISTINCT FROM in_money_pot_id 196 AND price_is_net=in_price_is_net; 197 IF NOT FOUND 198 THEN 199 out_conflict=TRUE; 200 out_no_cat=NULL; 201 RETURN; 202 END IF; 203 204 -- Check categories match as well 205 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 206 LOOP 207 ini_cat=ina_categories[i]; 208 209 PERFORM 210 FROM merchant_product_categories 211 WHERE product_serial=my_product_serial 212 AND category_serial=ini_cat; 213 IF NOT FOUND 214 THEN 215 out_conflict=TRUE; 216 out_no_cat=NULL; 217 RETURN; 218 END IF; 219 END LOOP; 220 221 -- Also check there are no additional categories 222 -- in either set. 223 SELECT COUNT(*) 224 INTO i 225 FROM merchant_product_categories 226 WHERE product_serial=my_product_serial; 227 IF i != COALESCE(array_length(ina_categories,1),0) 228 THEN 229 out_conflict=TRUE; 230 out_no_cat=NULL; 231 RETURN; 232 END IF; 233 234 -- Is idempotent! 235 out_conflict=FALSE; 236 out_no_cat=NULL; 237 RETURN; 238 END IF; 239 out_conflict=FALSE; 240 241 242 -- Add categories 243 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 244 LOOP 245 ini_cat=ina_categories[i]; 246 247 INSERT INTO merchant_product_categories 248 (product_serial 249 ,category_serial) 250 VALUES 251 (my_product_serial 252 ,ini_cat) 253 ON CONFLICT DO NOTHING; 254 255 END LOOP; 256 257 -- Success! 258 out_no_cat=NULL; 259 END $$;