select_open_transfers.sql (2472B)
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.select_open_transfers; 18 CREATE FUNCTION merchant.select_open_transfers(IN p_limit INT8) 19 RETURNS TABLE( 20 out_expected_credit_serial INT8, 21 out_instance_id TEXT, 22 out_exchange_url TEXT, 23 out_payto_uri TEXT, 24 out_wtid BYTEA, 25 out_retry_time INT8) 26 LANGUAGE plpgsql 27 AS $FN$ 28 DECLARE 29 rec RECORD; 30 s TEXT; 31 inner_rec RECORD; 32 remaining INT8 := p_limit; 33 BEGIN 34 FOR rec IN 35 SELECT merchant_serial 36 ,merchant_id 37 FROM merchant.merchant_instances 38 LOOP 39 EXIT WHEN remaining <= 0; 40 s := 'merchant_instance_' || rec.merchant_serial::TEXT; 41 BEGIN 42 FOR inner_rec IN 43 EXECUTE format( 44 'SELECT' 45 ' met.expected_credit_serial AS ecs' 46 ' ,met.exchange_url AS exu' 47 ' ,ma.payto_uri AS pu' 48 ' ,met.wtid AS wt' 49 ' ,met.retry_time AS rt' 50 ' FROM %I.merchant_expected_transfers met' 51 ' JOIN %I.merchant_accounts ma USING (account_serial)' 52 ' WHERE retry_needed' 53 ' ORDER BY retry_time ASC' 54 ' LIMIT $1', s, s) 55 USING remaining 56 LOOP 57 out_expected_credit_serial := inner_rec.ecs; 58 out_instance_id := rec.merchant_id; 59 out_exchange_url := inner_rec.exu; 60 out_payto_uri := inner_rec.pu; 61 out_wtid := inner_rec.wt; 62 out_retry_time := inner_rec.rt; 63 RETURN NEXT; 64 remaining := remaining - 1; 65 EXIT WHEN remaining <= 0; 66 END LOOP; 67 EXCEPTION 68 WHEN undefined_table 69 THEN 70 NULL; 71 END; 72 END LOOP; 73 END 74 $FN$; 75 COMMENT ON FUNCTION merchant.select_open_transfers(INT8) 76 IS 'Returns up to p_limit retry-needed expected_transfer rows across all' 77 ' instance schemas, joined with the per-instance merchant_accounts.';