pg_do_handle_inventory_changes.sql (8715B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 2025, 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 -- NOTE: do not use DROP, that would remove the TRIGGER! 18 CREATE OR REPLACE FUNCTION handle_inventory_changes() 19 RETURNS TRIGGER AS $$ 20 DECLARE 21 resolved_body TEXT; 22 webhook RECORD; -- To iterate over all matching webhooks 23 my_instance_id INT8; 24 my_event_type TEXT; 25 my_do_new BOOLEAN; 26 my_do_old BOOLEAN; 27 BEGIN 28 SELECT SUBSTRING(current_schema()::TEXT 29 FROM 'merchant_instance_([0-9]+)')::INT8 30 INTO my_instance_id; 31 32 CASE TG_OP 33 WHEN 'INSERT' THEN 34 my_event_type := 'inventory_added'; 35 my_do_new := TRUE; 36 my_do_old := FALSE; 37 WHEN 'DELETE' THEN 38 my_event_type := 'inventory_deleted'; 39 my_do_new := FALSE; 40 my_do_old := FALSE; 41 WHEN 'UPDATE' THEN 42 my_event_type := 'inventory_updated'; 43 my_do_new := TRUE; 44 my_do_old := TRUE; 45 IF (to_jsonb(OLD) - 'total_locked' - 'total_locked_frac') 46 IS NOT DISTINCT FROM 47 (to_jsonb(NEW) - 'total_locked' - 'total_locked_frac') 48 THEN 49 RETURN NULL; 50 END IF; 51 ELSE 52 RETURN NULL; 53 END CASE; 54 55 FOR webhook IN 56 SELECT 57 webhook_serial 58 ,url 59 ,http_method 60 ,body_template 61 FROM merchant_webhook 62 WHERE event_type = my_event_type 63 LOOP 64 -- Resolve placeholders for the current webhook 65 resolved_body := webhook.body_template; 66 IF my_do_new 67 THEN 68 resolved_body := merchant.replace_placeholder(resolved_body, 69 'webhook_type', 70 my_event_type); 71 resolved_body := merchant.replace_placeholder(resolved_body, 72 'product_serial', 73 NEW.product_serial::TEXT); 74 resolved_body := merchant.replace_placeholder(resolved_body, 75 'product_id', 76 NEW.product_id); 77 resolved_body := merchant.replace_placeholder(resolved_body, 78 'description', 79 NEW.description); 80 resolved_body := merchant.replace_placeholder(resolved_body, 81 'description_i18n', 82 NEW.description_i18n::TEXT); 83 resolved_body := merchant.replace_placeholder(resolved_body, 84 'unit', 85 NEW.unit); 86 resolved_body := merchant.replace_placeholder(resolved_body, 87 'image', 88 NEW.image); 89 resolved_body := merchant.replace_placeholder(resolved_body, 90 'taxes', 91 NEW.taxes::TEXT); 92 resolved_body := merchant.replace_placeholder(resolved_body, 93 'price', 94 NEW.price_array[1]::TEXT); 95 resolved_body := merchant.replace_placeholder(resolved_body, 96 'unit_price', 97 NEW.price_array::TEXT); 98 resolved_body := merchant.replace_placeholder(resolved_body, 99 'total_stock', 100 NEW.total_stock::TEXT); 101 resolved_body := merchant.replace_placeholder(resolved_body, 102 'total_sold', 103 NEW.total_sold::TEXT); 104 resolved_body := merchant.replace_placeholder(resolved_body, 105 'total_lost', 106 NEW.total_lost::TEXT); 107 resolved_body := merchant.replace_placeholder(resolved_body, 108 'address', 109 NEW.address::TEXT); 110 resolved_body := merchant.replace_placeholder(resolved_body, 111 'next_restock', 112 NEW.next_restock::TEXT); 113 resolved_body := merchant.replace_placeholder(resolved_body, 114 'minimum_age', 115 NEW.minimum_age::TEXT); 116 END IF; 117 IF my_do_old 118 THEN 119 resolved_body := merchant.replace_placeholder(resolved_body, 120 'old_description', 121 OLD.description); 122 resolved_body := merchant.replace_placeholder(resolved_body, 123 'old_description_i18n', 124 OLD.description_i18n::TEXT); 125 resolved_body := merchant.replace_placeholder(resolved_body, 126 'old_unit', 127 OLD.unit); 128 resolved_body := merchant.replace_placeholder(resolved_body, 129 'old_image', 130 OLD.image); 131 resolved_body := merchant.replace_placeholder(resolved_body, 132 'old_taxes', 133 OLD.taxes::TEXT); 134 resolved_body := merchant.replace_placeholder(resolved_body, 135 'old_price', 136 OLD.price_array[1]::TEXT); 137 resolved_body := merchant.replace_placeholder(resolved_body, 138 'old_unit_price', 139 OLD.price_array::TEXT); 140 resolved_body := merchant.replace_placeholder(resolved_body, 141 'old_total_stock', 142 OLD.total_stock::TEXT); 143 resolved_body := merchant.replace_placeholder(resolved_body, 144 'old_total_sold', 145 OLD.total_sold::TEXT); 146 resolved_body := merchant.replace_placeholder(resolved_body, 147 'old_total_lost', 148 OLD.total_lost::TEXT); 149 resolved_body := merchant.replace_placeholder(resolved_body, 150 'old_address', 151 OLD.address::TEXT); 152 resolved_body := merchant.replace_placeholder(resolved_body, 153 'old_next_restock', 154 OLD.next_restock::TEXT); 155 resolved_body := merchant.replace_placeholder(resolved_body, 156 'old_minimum_age', 157 OLD.minimum_age::TEXT); 158 END IF; 159 -- Insert into pending webhooks for this webhook 160 INSERT INTO merchant.merchant_pending_webhooks 161 (merchant_serial 162 ,webhook_serial 163 ,url 164 ,http_method 165 ,body 166 ) VALUES ( 167 my_instance_id 168 ,webhook.webhook_serial 169 ,webhook.url 170 ,webhook.http_method 171 ,resolved_body 172 ); 173 END LOOP; 174 175 -- Notify the webhook service 176 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 177 RETURN NULL; 178 END; 179 $$ LANGUAGE plpgsql; 180 181 COMMENT ON FUNCTION handle_inventory_changes 182 IS 'Function to handle inventory changes and notify webhooks';