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