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