insert_product.sql (5716B)
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 LANGUAGE plpgsql 43 AS $$ 44 DECLARE 45 my_product_serial INT8; 46 i INT8; 47 ini_cat INT8; 48 BEGIN 49 50 out_no_group = FALSE; 51 out_no_pot = FALSE; 52 53 IF in_product_group_id IS NOT NULL 54 THEN 55 PERFORM FROM merchant_product_groups 56 WHERE product_group_serial=in_product_group_id; 57 IF NOT FOUND 58 THEN 59 out_no_group=TRUE; 60 out_conflict=FALSE; 61 out_no_cat=NULL; 62 RETURN; 63 END IF; 64 END IF; 65 66 IF in_money_pot_id IS NOT NULL 67 THEN 68 PERFORM FROM merchant_money_pots 69 WHERE money_pot_serial=in_money_pot_id; 70 IF NOT FOUND 71 THEN 72 out_no_pot=TRUE; 73 out_conflict=FALSE; 74 out_no_cat=NULL; 75 RETURN; 76 END IF; 77 END IF; 78 79 -- Check all categories exist. Must happen before we modify anything, 80 -- as we do not roll back on the out_no_cat return path. 81 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 82 LOOP 83 ini_cat=ina_categories[i]; 84 85 PERFORM 86 FROM merchant_categories 87 WHERE category_serial=ini_cat; 88 IF NOT FOUND 89 THEN 90 out_no_cat=i; 91 out_conflict=FALSE; 92 RETURN; 93 END IF; 94 END LOOP; 95 96 INSERT INTO merchant_inventory 97 (product_id 98 ,product_name 99 ,description 100 ,description_i18n 101 ,unit 102 ,image 103 ,image_hash 104 ,taxes 105 ,price_array 106 ,total_stock 107 ,total_stock_frac 108 ,allow_fractional_quantity 109 ,fractional_precision_level 110 ,address 111 ,next_restock 112 ,minimum_age 113 ,product_group_serial 114 ,money_pot_serial 115 ,price_is_net 116 ) VALUES ( 117 in_product_id 118 ,in_product_name 119 ,in_description 120 ,in_description_i18n 121 ,in_unit 122 ,in_image 123 ,CASE 124 WHEN (in_image IS NULL) OR (in_image = '') 125 THEN NULL 126 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 127 'sha256'), 128 'hex') 129 END 130 ,in_taxes 131 ,ina_price_list 132 ,in_total_stock 133 ,in_total_stock_frac 134 ,in_allow_fractional_quantity 135 ,in_fractional_precision_level 136 ,in_address 137 ,in_next_restock 138 ,in_minimum_age 139 ,in_product_group_id 140 ,in_money_pot_id 141 ,in_price_is_net 142 ) 143 ON CONFLICT (product_id) DO NOTHING 144 RETURNING product_serial 145 INTO my_product_serial; 146 147 148 IF NOT FOUND 149 THEN 150 -- Check for idempotency 151 SELECT product_serial 152 INTO my_product_serial 153 FROM merchant_inventory 154 WHERE product_id=in_product_id 155 AND product_name=in_product_name 156 AND description=in_description 157 AND description_i18n=in_description_i18n 158 AND unit=in_unit 159 AND image=in_image 160 AND taxes=in_taxes 161 AND to_jsonb(COALESCE(price_array, ARRAY[]::merchant.taler_amount_currency[])) 162 = to_jsonb(COALESCE(ina_price_list, ARRAY[]::merchant.taler_amount_currency[])) -- FIXME: wild. Why so complicated? 163 AND total_stock=in_total_stock 164 AND total_stock_frac=in_total_stock_frac 165 AND allow_fractional_quantity=in_allow_fractional_quantity 166 AND fractional_precision_level=in_fractional_precision_level 167 AND address=in_address 168 AND next_restock=in_next_restock 169 AND minimum_age=in_minimum_age 170 AND product_group_serial IS NOT DISTINCT FROM in_product_group_id 171 AND money_pot_serial IS NOT DISTINCT FROM in_money_pot_id 172 AND price_is_net=in_price_is_net; 173 IF NOT FOUND 174 THEN 175 out_conflict=TRUE; 176 out_no_cat=NULL; 177 RETURN; 178 END IF; 179 180 -- Check categories match as well 181 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 182 LOOP 183 ini_cat=ina_categories[i]; 184 185 PERFORM 186 FROM merchant_product_categories 187 WHERE product_serial=my_product_serial 188 AND category_serial=ini_cat; 189 IF NOT FOUND 190 THEN 191 out_conflict=TRUE; 192 out_no_cat=NULL; 193 RETURN; 194 END IF; 195 END LOOP; 196 197 -- Also check there are no additional categories 198 -- in either set. 199 SELECT COUNT(*) 200 INTO i 201 FROM merchant_product_categories 202 WHERE product_serial=my_product_serial; 203 IF i != COALESCE(array_length(ina_categories,1),0) 204 THEN 205 out_conflict=TRUE; 206 out_no_cat=NULL; 207 RETURN; 208 END IF; 209 210 -- Is idempotent! 211 out_conflict=FALSE; 212 out_no_cat=NULL; 213 RETURN; 214 END IF; 215 out_conflict=FALSE; 216 217 218 -- Add categories 219 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 220 LOOP 221 ini_cat=ina_categories[i]; 222 223 INSERT INTO merchant_product_categories 224 (product_serial 225 ,category_serial) 226 VALUES 227 (my_product_serial 228 ,ini_cat) 229 ON CONFLICT DO NOTHING; 230 231 END LOOP; 232 233 -- Success! 234 out_no_cat=NULL; 235 END $$;