pg_do_handle_inventory_changes.sql (14598B)
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 -- 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 my_merchant_serial BIGINT; 22 resolved_body TEXT; 23 webhook RECORD; -- To iterate over all matching webhooks 24 BEGIN 25 -- Fetch the merchant_serial directly from the NEW or OLD row 26 my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial); 27 28 -- INSERT case: Notify webhooks for inventory addition 29 IF TG_OP = 'INSERT' THEN 30 FOR webhook IN 31 SELECT webhook_serial, 32 merchant_serial, 33 url, 34 http_method, 35 body_template 36 FROM merchant.merchant_webhook 37 WHERE event_type = 'inventory_added' 38 AND merchant_serial = my_merchant_serial 39 LOOP 40 -- Resolve placeholders for the current webhook 41 resolved_body := webhook.body_template; 42 resolved_body := replace_placeholder(resolved_body, 43 'webhook_type', 44 'inventory_added'); 45 resolved_body := replace_placeholder(resolved_body, 46 'product_serial', 47 NEW.product_serial::TEXT); 48 resolved_body := replace_placeholder(resolved_body, 49 'product_id', 50 NEW.product_id); 51 resolved_body := replace_placeholder(resolved_body, 52 'description', 53 NEW.description); 54 resolved_body := replace_placeholder(resolved_body, 55 'description_i18n', 56 NEW.description_i18n::TEXT); 57 resolved_body := replace_placeholder(resolved_body, 58 'unit', 59 NEW.unit); 60 resolved_body := replace_placeholder(resolved_body, 61 'image', 62 NEW.image); 63 resolved_body := replace_placeholder(resolved_body, 64 'taxes', 65 NEW.taxes::TEXT); 66 resolved_body := replace_placeholder(resolved_body, 67 'price', 68 NEW.price::TEXT); 69 resolved_body := replace_placeholder(resolved_body, 70 'total_stock', 71 NEW.total_stock::TEXT); 72 resolved_body := replace_placeholder(resolved_body, 73 'total_sold', 74 NEW.total_sold::TEXT); 75 resolved_body := replace_placeholder(resolved_body, 76 'total_lost', 77 NEW.total_lost::TEXT); 78 resolved_body := replace_placeholder(resolved_body, 79 'address', 80 NEW.address::TEXT); 81 resolved_body := replace_placeholder(resolved_body, 82 'next_restock', 83 NEW.next_restock::TEXT); 84 resolved_body := replace_placeholder(resolved_body, 85 'minimum_age', 86 NEW.minimum_age::TEXT); 87 88 -- Insert into pending webhooks for this webhook 89 INSERT INTO merchant.merchant_pending_webhooks 90 (merchant_serial, webhook_serial, url, http_method, body) 91 VALUES 92 (webhook.merchant_serial, 93 webhook.webhook_serial, 94 webhook.url, 95 webhook.http_method, 96 resolved_body); 97 END LOOP; 98 99 -- Notify the webhook service 100 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 101 END IF; 102 103 -- UPDATE case: Notify webhooks for inventory update 104 IF TG_OP = 'UPDATE' THEN 105 FOR webhook IN 106 SELECT webhook_serial, 107 merchant_serial, 108 url, 109 http_method, 110 body_template 111 FROM merchant.merchant_webhook 112 WHERE event_type = 'inventory_updated' 113 AND merchant_serial = my_merchant_serial 114 LOOP 115 -- Resolve placeholders for the current webhook 116 resolved_body := webhook.body_template; 117 resolved_body := replace_placeholder(resolved_body, 118 'webhook_type', 119 'inventory_updated'); 120 resolved_body := replace_placeholder(resolved_body, 121 'product_serial', 122 NEW.product_serial::TEXT); 123 resolved_body := replace_placeholder(resolved_body, 124 'product_id', 125 NEW.product_id); 126 resolved_body := replace_placeholder(resolved_body, 127 'old_description', 128 OLD.description); 129 resolved_body := replace_placeholder(resolved_body, 130 'description', 131 NEW.description); 132 resolved_body := replace_placeholder(resolved_body, 133 'old_description_i18n', 134 OLD.description_i18n::TEXT); 135 resolved_body := replace_placeholder(resolved_body, 136 'description_i18n', 137 NEW.description_i18n::TEXT); 138 resolved_body := replace_placeholder(resolved_body, 139 'old_unit', 140 OLD.unit); 141 resolved_body := replace_placeholder(resolved_body, 142 'unit', 143 NEW.unit); 144 resolved_body := replace_placeholder(resolved_body, 145 'old_image', 146 OLD.image); 147 resolved_body := replace_placeholder(resolved_body, 148 'image', 149 NEW.image); 150 resolved_body := replace_placeholder(resolved_body, 151 'old_taxes', 152 OLD.taxes::TEXT); 153 resolved_body := replace_placeholder(resolved_body, 154 'taxes', 155 NEW.taxes::TEXT); 156 resolved_body := replace_placeholder(resolved_body, 157 'old_price', 158 OLD.price::TEXT); 159 resolved_body := replace_placeholder(resolved_body, 160 'price', 161 NEW.price::TEXT); 162 resolved_body := replace_placeholder(resolved_body, 163 'old_total_stock', 164 OLD.total_stock::TEXT); 165 resolved_body := replace_placeholder(resolved_body, 166 'total_stock', 167 NEW.total_stock::TEXT); 168 resolved_body := replace_placeholder(resolved_body, 169 'old_total_sold', 170 OLD.total_sold::TEXT); 171 resolved_body := replace_placeholder(resolved_body, 172 'total_sold', 173 NEW.total_sold::TEXT); 174 resolved_body := replace_placeholder(resolved_body, 175 'old_total_lost', 176 OLD.total_lost::TEXT); 177 resolved_body := replace_placeholder(resolved_body, 178 'total_lost', 179 NEW.total_lost::TEXT); 180 resolved_body := replace_placeholder(resolved_body, 181 'old_address', 182 OLD.address::TEXT); 183 resolved_body := replace_placeholder(resolved_body, 184 'address', 185 NEW.address::TEXT); 186 resolved_body := replace_placeholder(resolved_body, 187 'old_next_restock', 188 OLD.next_restock::TEXT); 189 resolved_body := replace_placeholder(resolved_body, 190 'next_restock', 191 NEW.next_restock::TEXT); 192 resolved_body := replace_placeholder(resolved_body, 193 'old_minimum_age', 194 OLD.minimum_age::TEXT); 195 resolved_body := replace_placeholder(resolved_body, 196 'minimum_age', 197 NEW.minimum_age::TEXT); 198 199 -- Insert into pending webhooks for this webhook 200 INSERT INTO merchant.merchant_pending_webhooks 201 (merchant_serial, webhook_serial, url, http_method, body) 202 VALUES 203 (webhook.merchant_serial, 204 webhook.webhook_serial, 205 webhook.url, 206 webhook.http_method, 207 resolved_body); 208 END LOOP; 209 210 -- Notify the webhook service 211 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 212 END IF; 213 214 -- DELETE case: Notify webhooks for inventory deletion 215 IF TG_OP = 'DELETE' THEN 216 FOR webhook IN 217 SELECT webhook_serial, 218 merchant_serial, 219 url, 220 http_method, 221 body_template 222 FROM merchant.merchant_webhook 223 WHERE event_type = 'inventory_deleted' 224 AND merchant_serial = my_merchant_serial 225 LOOP 226 -- Resolve placeholders for the current webhook 227 resolved_body := webhook.body_template; 228 resolved_body := replace_placeholder(resolved_body, 229 'webhook_type', 230 'inventory_deleted'); 231 resolved_body := replace_placeholder(resolved_body, 232 'product_serial', 233 OLD.product_serial::TEXT); 234 resolved_body := replace_placeholder(resolved_body, 235 'product_id', 236 OLD.product_id); 237 resolved_body := replace_placeholder(resolved_body, 238 'description', 239 OLD.description); 240 resolved_body := replace_placeholder(resolved_body, 241 'description_i18n', 242 OLD.description_i18n::TEXT); 243 resolved_body := replace_placeholder(resolved_body, 244 'unit', 245 OLD.unit); 246 resolved_body := replace_placeholder(resolved_body, 247 'image', 248 OLD.image); 249 resolved_body := replace_placeholder(resolved_body, 250 'taxes', 251 OLD.taxes::TEXT); 252 resolved_body := replace_placeholder(resolved_body, 253 'price', 254 OLD.price::TEXT); 255 resolved_body := replace_placeholder(resolved_body, 256 'total_stock', 257 OLD.total_stock::TEXT); 258 resolved_body := replace_placeholder(resolved_body, 259 'total_sold', 260 OLD.total_sold::TEXT); 261 resolved_body := replace_placeholder(resolved_body, 262 'total_lost', 263 OLD.total_lost::TEXT); 264 resolved_body := replace_placeholder(resolved_body, 265 'address', 266 OLD.address::TEXT); 267 resolved_body := replace_placeholder(resolved_body, 268 'next_restock', 269 OLD.next_restock::TEXT); 270 resolved_body := replace_placeholder(resolved_body, 271 'minimum_age', 272 OLD.minimum_age::TEXT); 273 274 -- Insert into pending webhooks for this webhook 275 INSERT INTO merchant.merchant_pending_webhooks 276 (merchant_serial, webhook_serial, url, http_method, body) 277 VALUES 278 (webhook.merchant_serial, 279 webhook.webhook_serial, 280 webhook.url, 281 webhook.http_method, 282 resolved_body); 283 END LOOP; 284 285 -- Notify the webhook service 286 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 287 END IF; 288 289 RETURN NULL; 290 END; 291 $$ LANGUAGE plpgsql; 292 293 COMMENT ON FUNCTION handle_inventory_changes 294 IS 'Function to handle inventory changes and notify webhooks';