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