pg_do_handle_category_changes.sql (6542B)
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_category_changes() 19 RETURNS TRIGGER AS $$ 20 DECLARE 21 my_merchant_serial BIGINT; 22 resolved_body TEXT; 23 webhook RECORD; -- To iterate over all webhooks matching the event type 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: Add a webhook for category 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 = 'category_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 'category_added'); 45 resolved_body := replace_placeholder(resolved_body, 46 'category_serial', 47 NEW.category_serial::TEXT); 48 resolved_body := replace_placeholder(resolved_body, 49 'category_name', 50 NEW.category_name); 51 resolved_body := replace_placeholder(resolved_body, 52 'merchant_serial', 53 my_merchant_serial::TEXT); 54 55 -- Insert into pending webhooks for this webhook 56 INSERT INTO merchant.merchant_pending_webhooks 57 (merchant_serial, webhook_serial, url, http_method, body) 58 VALUES 59 (webhook.merchant_serial, 60 webhook.webhook_serial, 61 webhook.url, 62 webhook.http_method, 63 resolved_body); 64 END LOOP; 65 66 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 67 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 68 END IF; 69 70 -- UPDATE case: Add a webhook for category update 71 IF TG_OP = 'UPDATE' THEN 72 FOR webhook IN 73 SELECT webhook_serial, 74 merchant_serial, 75 url, 76 http_method, 77 body_template 78 FROM merchant.merchant_webhook 79 WHERE event_type = 'category_updated' 80 AND merchant_serial = my_merchant_serial 81 LOOP 82 -- Resolve placeholders for the current webhook 83 resolved_body := webhook.body_template; 84 resolved_body := replace_placeholder(resolved_body, 85 'webhook_type', 86 'category_updated'); 87 resolved_body := replace_placeholder(resolved_body, 88 'category_serial', 89 NEW.category_serial::TEXT); 90 resolved_body := replace_placeholder(resolved_body, 91 'old_category_name', 92 OLD.category_name); 93 resolved_body := replace_placeholder(resolved_body, 94 'category_name', 95 NEW.category_name); 96 resolved_body := replace_placeholder(resolved_body, 97 'category_name_i18n', 98 NEW.category_name_i18n::TEXT); 99 resolved_body := replace_placeholder(resolved_body, 100 'old_category_name_i18n', 101 OLD.category_name_i18n::TEXT); 102 103 -- Insert into pending webhooks for this webhook 104 INSERT INTO merchant.merchant_pending_webhooks 105 (merchant_serial, webhook_serial, url, http_method, body) 106 VALUES 107 (webhook.merchant_serial, 108 webhook.webhook_serial, 109 webhook.url, 110 webhook.http_method, 111 resolved_body); 112 END LOOP; 113 114 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 115 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 116 END IF; 117 118 -- DELETE case: Add a webhook for category deletion 119 IF TG_OP = 'DELETE' THEN 120 FOR webhook IN 121 SELECT webhook_serial, 122 merchant_serial, 123 url, 124 http_method, 125 body_template 126 FROM merchant.merchant_webhook 127 WHERE event_type = 'category_deleted' 128 AND merchant_serial = my_merchant_serial 129 LOOP 130 -- Resolve placeholders for the current webhook 131 resolved_body := webhook.body_template; 132 resolved_body := replace_placeholder(resolved_body, 133 'webhook_type', 134 'category_deleted'); 135 resolved_body := replace_placeholder(resolved_body, 136 'category_serial', 137 OLD.category_serial::TEXT); 138 resolved_body := replace_placeholder(resolved_body, 139 'category_name', 140 OLD.category_name); 141 142 -- Insert into pending webhooks for this webhook 143 INSERT INTO merchant.merchant_pending_webhooks 144 (merchant_serial, webhook_serial, url, http_method, body) 145 VALUES 146 (webhook.merchant_serial, 147 webhook.webhook_serial, 148 webhook.url, 149 webhook.http_method, 150 resolved_body); 151 END LOOP; 152 153 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 154 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 155 END IF; 156 157 RETURN NULL; 158 END; 159 $$ LANGUAGE plpgsql; 160 161 COMMENT ON FUNCTION handle_category_changes 162 IS 'Trigger function to handle pending webhooks for category changes';