insert_product.sql (5388B)
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 INSERT INTO merchant_inventory 80 (product_id 81 ,product_name 82 ,description 83 ,description_i18n 84 ,unit 85 ,image 86 ,image_hash 87 ,taxes 88 ,price_array 89 ,total_stock 90 ,total_stock_frac 91 ,allow_fractional_quantity 92 ,fractional_precision_level 93 ,address 94 ,next_restock 95 ,minimum_age 96 ,product_group_serial 97 ,money_pot_serial 98 ,price_is_net 99 ) VALUES ( 100 in_product_id 101 ,in_product_name 102 ,in_description 103 ,in_description_i18n 104 ,in_unit 105 ,in_image 106 ,CASE 107 WHEN (in_image IS NULL) OR (in_image = '') 108 THEN NULL 109 ELSE encode(public.digest(convert_to(in_image, 'UTF8'), 110 'sha256'), 111 'hex') 112 END 113 ,in_taxes 114 ,ina_price_list 115 ,in_total_stock 116 ,in_total_stock_frac 117 ,in_allow_fractional_quantity 118 ,in_fractional_precision_level 119 ,in_address 120 ,in_next_restock 121 ,in_minimum_age 122 ,in_product_group_id 123 ,in_money_pot_id 124 ,in_price_is_net 125 ) 126 ON CONFLICT (product_id) DO NOTHING 127 RETURNING product_serial 128 INTO my_product_serial; 129 130 131 IF NOT FOUND 132 THEN 133 -- Check for idempotency 134 SELECT product_serial 135 INTO my_product_serial 136 FROM merchant_inventory 137 WHERE product_id=in_product_id 138 AND product_name=in_product_name 139 AND description=in_description 140 AND description_i18n=in_description_i18n 141 AND unit=in_unit 142 AND image=in_image 143 AND taxes=in_taxes 144 AND to_jsonb(COALESCE(price_array, ARRAY[]::merchant.taler_amount_currency[])) 145 = to_jsonb(COALESCE(ina_price_list, ARRAY[]::merchant.taler_amount_currency[])) -- FIXME: wild. Why so complicated? 146 AND total_stock=in_total_stock 147 AND total_stock_frac=in_total_stock_frac 148 AND allow_fractional_quantity=in_allow_fractional_quantity 149 AND fractional_precision_level=in_fractional_precision_level 150 AND address=in_address 151 AND next_restock=in_next_restock 152 AND minimum_age=in_minimum_age 153 AND product_group_serial IS NOT DISTINCT FROM in_product_group_id 154 AND money_pot_serial IS NOT DISTINCT FROM in_money_pot_id 155 AND price_is_net=in_price_is_net; 156 IF NOT FOUND 157 THEN 158 out_conflict=TRUE; 159 out_no_cat=NULL; 160 RETURN; 161 END IF; 162 163 -- Check categories match as well 164 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 165 LOOP 166 ini_cat=ina_categories[i]; 167 168 PERFORM 169 FROM merchant_product_categories 170 WHERE product_serial=my_product_serial 171 AND category_serial=ini_cat; 172 IF NOT FOUND 173 THEN 174 out_conflict=TRUE; 175 out_no_cat=NULL; 176 RETURN; 177 END IF; 178 END LOOP; 179 180 -- Also check there are no additional categories 181 -- in either set. 182 SELECT COUNT(*) 183 INTO i 184 FROM merchant_product_categories 185 WHERE product_serial=my_product_serial; 186 IF i != COALESCE(array_length(ina_categories,1),0) 187 THEN 188 out_conflict=TRUE; 189 out_no_cat=NULL; 190 RETURN; 191 END IF; 192 193 -- Is idempotent! 194 out_conflict=FALSE; 195 out_no_cat=NULL; 196 RETURN; 197 END IF; 198 out_conflict=FALSE; 199 200 201 -- Add categories 202 FOR i IN 1..COALESCE(array_length(ina_categories,1),0) 203 LOOP 204 ini_cat=ina_categories[i]; 205 206 INSERT INTO merchant_product_categories 207 (product_serial 208 ,category_serial) 209 VALUES 210 (my_product_serial 211 ,ini_cat) 212 ON CONFLICT DO NOTHING; 213 214 IF NOT FOUND 215 THEN 216 out_no_cat=i; 217 RETURN; 218 END IF; 219 END LOOP; 220 221 -- Success! 222 out_no_cat=NULL; 223 END $$;