delete_product.sql (2623B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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_delete_product; 18 CREATE FUNCTION merchant_do_delete_product ( 19 IN in_product_id TEXT, 20 IN in_force BOOLEAN, 21 OUT out_not_found BOOLEAN, 22 OUT out_locked BOOLEAN) 23 LANGUAGE plpgsql 24 AS $$ 25 DECLARE 26 my_product_serial INT8; 27 my_has_locks BOOLEAN; 28 BEGIN 29 SELECT product_serial 30 INTO my_product_serial 31 FROM merchant_inventory 32 WHERE product_id = in_product_id; 33 IF NOT FOUND 34 THEN 35 out_not_found = TRUE; 36 out_locked = FALSE; 37 RETURN; 38 END IF; 39 out_not_found = FALSE; 40 41 my_has_locks = ( 42 EXISTS (SELECT 1 43 FROM merchant_order_locks 44 WHERE product_serial = my_product_serial) 45 OR EXISTS (SELECT 1 46 FROM merchant_inventory_locks 47 WHERE product_serial = my_product_serial)); 48 IF (my_has_locks AND NOT in_force) 49 THEN 50 -- Refuse: the product is locked and the caller did not force deletion. 51 out_locked = TRUE; 52 RETURN; 53 END IF; 54 out_locked = FALSE; 55 56 IF (my_has_locks) 57 THEN 58 -- Release any locks first. merchant_order_locks has no ON DELETE CASCADE 59 -- on product_serial, so its rows would otherwise block the deletion; 60 -- merchant_inventory_locks rows would cascade, but we delete them 61 -- explicitly so that the total_locked counter trigger stays consistent. 62 DELETE FROM merchant_order_locks 63 WHERE product_serial = my_product_serial; 64 DELETE FROM merchant_inventory_locks 65 WHERE product_serial = my_product_serial; 66 END IF; 67 DELETE FROM merchant_inventory 68 WHERE product_serial = my_product_serial; 69 END $$; 70 71 COMMENT ON FUNCTION merchant_do_delete_product(TEXT, BOOLEAN) 72 IS 'Deletes a product. Returns out_not_found=TRUE if the product does not' 73 ' exist. If the product is locked and in_force is FALSE, returns' 74 ' out_locked=TRUE without deleting. If in_force is TRUE, any locks' 75 ' are released and the product is deleted.';