iterate_outdated_kyc_statuses.sql (2085B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 DROP FUNCTION IF EXISTS merchant.account_kyc_get_outdated; 18 CREATE FUNCTION merchant.account_kyc_get_outdated( 19 IN in_now INT8, 20 IN in_merchant_serial INT8 21 ) 22 RETURNS TABLE( 23 out_merchant_id TEXT, 24 out_h_wire BYTEA, 25 out_exchange_url TEXT) 26 LANGUAGE plpgsql 27 AS $FN$ 28 DECLARE 29 rec RECORD; 30 s TEXT; 31 inner_rec RECORD; 32 BEGIN 33 FOR rec IN 34 SELECT merchant_serial 35 ,merchant_id 36 FROM merchant.merchant_instances 37 WHERE merchant_serial=in_merchant_serial 38 LOOP 39 s := 'merchant_instance_' || rec.merchant_serial::TEXT; 40 BEGIN 41 FOR inner_rec 42 IN 43 EXECUTE format('SELECT ma.h_wire AS h_wire, kyc.exchange_url AS exchange_url' 44 ' FROM %I.merchant_kyc kyc' 45 ' JOIN %I.merchant_accounts ma USING (account_serial)' 46 ' WHERE kyc.next_kyc_poll <= $1 AND ma.active' 47 ' ORDER BY kyc.next_kyc_poll ASC', s, s) 48 USING in_now 49 LOOP 50 out_merchant_id := rec.merchant_id; 51 out_h_wire := inner_rec.h_wire; 52 out_exchange_url := inner_rec.exchange_url; 53 RETURN NEXT; 54 END LOOP; 55 EXCEPTION 56 WHEN undefined_table 57 THEN 58 NULL; 59 END; 60 END LOOP; 61 END 62 $FN$; 63 COMMENT ON FUNCTION merchant.account_kyc_get_outdated(INT8, INT8) 64 IS 'Returns due KYC entries for active accounts of the specified instance only.';