pg_do_handle_category_changes.sql (6574B)
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 resolved_body TEXT; 22 webhook RECORD; -- To iterate over all webhooks matching the event type 23 my_instance_id INT8; 24 BEGIN 25 SELECT SUBSTRING(current_schema()::TEXT 26 FROM 'merchant_instance_([0-9]+)')::INT8 27 INTO my_instance_id; 28 29 -- INSERT case: Add a webhook for category addition 30 IF TG_OP = 'INSERT' THEN 31 FOR webhook IN 32 SELECT 33 webhook_serial 34 ,url 35 ,http_method 36 ,body_template 37 FROM merchant_webhook 38 WHERE event_type = 'category_added' 39 LOOP 40 -- Resolve placeholders for the current webhook 41 resolved_body := webhook.body_template; 42 resolved_body := merchant.replace_placeholder(resolved_body, 43 'webhook_type', 44 'category_added'); 45 resolved_body := merchant.replace_placeholder(resolved_body, 46 'category_serial', 47 NEW.category_serial::TEXT); 48 resolved_body := merchant.replace_placeholder(resolved_body, 49 'category_name', 50 NEW.category_name); 51 resolved_body := merchant.replace_placeholder(resolved_body, 52 'merchant_serial', 53 my_instance_id::TEXT); 54 55 -- Insert into pending webhooks for this webhook 56 INSERT INTO merchant.merchant_pending_webhooks 57 (merchant_serial 58 ,webhook_serial 59 ,url 60 ,http_method 61 ,body 62 ) VALUES ( 63 my_instance_id, 64 webhook.webhook_serial, 65 webhook.url, 66 webhook.http_method, 67 resolved_body 68 ); 69 END LOOP; 70 71 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 72 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 73 END IF; 74 75 -- UPDATE case: Add a webhook for category update 76 IF TG_OP = 'UPDATE' THEN 77 FOR webhook IN 78 SELECT 79 webhook_serial, 80 url, 81 http_method, 82 body_template 83 FROM merchant_webhook 84 WHERE event_type = 'category_updated' 85 LOOP 86 -- Resolve placeholders for the current webhook 87 resolved_body := webhook.body_template; 88 resolved_body := merchant.replace_placeholder(resolved_body, 89 'webhook_type', 90 'category_updated'); 91 resolved_body := merchant.replace_placeholder(resolved_body, 92 'category_serial', 93 NEW.category_serial::TEXT); 94 resolved_body := merchant.replace_placeholder(resolved_body, 95 'old_category_name', 96 OLD.category_name); 97 resolved_body := merchant.replace_placeholder(resolved_body, 98 'category_name', 99 NEW.category_name); 100 resolved_body := merchant.replace_placeholder(resolved_body, 101 'category_name_i18n', 102 NEW.category_name_i18n::TEXT); 103 resolved_body := merchant.replace_placeholder(resolved_body, 104 'old_category_name_i18n', 105 OLD.category_name_i18n::TEXT); 106 107 -- Insert into pending webhooks for this webhook 108 INSERT INTO merchant.merchant_pending_webhooks 109 (merchant_serial 110 ,webhook_serial 111 ,url 112 ,http_method 113 ,body 114 ) VALUES ( 115 my_instance_id 116 ,webhook.webhook_serial 117 ,webhook.url 118 ,webhook.http_method 119 ,resolved_body 120 ); 121 END LOOP; 122 123 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 124 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 125 END IF; 126 127 -- DELETE case: Add a webhook for category deletion 128 IF TG_OP = 'DELETE' THEN 129 FOR webhook IN 130 SELECT 131 webhook_serial 132 ,url 133 ,http_method 134 ,body_template 135 FROM merchant_webhook 136 WHERE event_type = 'category_deleted' 137 LOOP 138 -- Resolve placeholders for the current webhook 139 resolved_body := webhook.body_template; 140 resolved_body := merchant.replace_placeholder(resolved_body, 141 'webhook_type', 142 'category_deleted'); 143 resolved_body := merchant.replace_placeholder(resolved_body, 144 'category_serial', 145 OLD.category_serial::TEXT); 146 resolved_body := merchant.replace_placeholder(resolved_body, 147 'category_name', 148 OLD.category_name); 149 150 -- Insert into pending webhooks for this webhook 151 INSERT INTO merchant.merchant_pending_webhooks 152 (merchant_serial 153 ,webhook_serial 154 ,url 155 ,http_method 156 ,body 157 ) VALUES ( 158 my_instance_id 159 ,webhook.webhook_serial 160 ,webhook.url 161 ,webhook.http_method 162 ,resolved_body 163 ); 164 END LOOP; 165 166 -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING 167 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 168 END IF; 169 170 RETURN NULL; 171 END; 172 $$ LANGUAGE plpgsql; 173 174 COMMENT ON FUNCTION handle_category_changes 175 IS 'Trigger function to handle pending webhooks for category changes';