lookup_pending_deposits.sql (3931B)
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.lookup_pending_deposits(TEXT, INT8, INT8, BOOLEAN); 18 CREATE FUNCTION merchant.lookup_pending_deposits( 19 IN p_exchange_url TEXT, 20 IN p_now INT8, 21 IN p_limit INT8, 22 IN p_allow_future BOOLEAN) 23 RETURNS TABLE( 24 out_deposit_serial INT8, 25 out_h_contract_terms BYTEA, 26 out_merchant_priv BYTEA, 27 out_merchant_id TEXT, 28 out_wire_transfer_deadline INT8, 29 out_retry_time INT8, 30 out_h_wire BYTEA, 31 out_amount_with_fee merchant.taler_amount_currency, 32 out_deposit_fee merchant.taler_amount_currency, 33 out_coin_pub BYTEA) 34 LANGUAGE plpgsql 35 AS $FN$ 36 DECLARE 37 rec RECORD; 38 s TEXT; 39 inner_rec RECORD; 40 remaining INT8 := p_limit; 41 BEGIN 42 FOR rec IN 43 SELECT merchant_serial 44 ,merchant_id 45 ,merchant_priv 46 FROM merchant.merchant_instances 47 LOOP 48 EXIT WHEN remaining <= 0; 49 s := 'merchant_instance_' || rec.merchant_serial::TEXT; 50 BEGIN 51 FOR inner_rec IN 52 EXECUTE format( 53 'SELECT' 54 ' md.deposit_serial AS deposit_serial' 55 ' ,mct.h_contract_terms AS h_contract_terms' 56 ' ,mdc.wire_transfer_deadline AS wire_transfer_deadline' 57 ' ,md.settlement_retry_time AS retry_time' 58 ' ,ma.h_wire AS h_wire' 59 ' ,md.amount_with_fee AS amount_with_fee' 60 ' ,md.deposit_fee AS deposit_fee' 61 ' ,md.coin_pub AS coin_pub' 62 ' FROM %I.merchant_deposits md' 63 ' JOIN %I.merchant_deposit_confirmations mdc' 64 ' USING (deposit_confirmation_serial)' 65 ' JOIN %I.merchant_contract_terms mct' 66 ' ON (mct.order_serial=mdc.order_serial)' 67 ' JOIN %I.merchant_accounts ma' 68 ' ON (mdc.account_serial=ma.account_serial)' 69 ' LEFT JOIN %I.merchant_kyc kyc' 70 ' ON (mdc.account_serial=kyc.account_serial)' 71 ' WHERE (mdc.exchange_url=$1)' 72 ' AND md.settlement_retry_needed' 73 ' AND ($4 OR (md.settlement_retry_time < $2))' 74 ' AND ( (kyc.kyc_ok IS NULL) OR kyc.kyc_ok)' 75 ' ORDER BY md.settlement_retry_time ASC' 76 ' LIMIT $3', 77 s, s, s, s, s) 78 USING p_exchange_url 79 ,p_now 80 ,remaining 81 ,p_allow_future 82 LOOP 83 out_deposit_serial := inner_rec.deposit_serial; 84 out_h_contract_terms := inner_rec.h_contract_terms; 85 out_merchant_priv := rec.merchant_priv; 86 out_merchant_id := rec.merchant_id; 87 out_wire_transfer_deadline := inner_rec.wire_transfer_deadline; 88 out_retry_time := inner_rec.retry_time; 89 out_h_wire := inner_rec.h_wire; 90 out_amount_with_fee := inner_rec.amount_with_fee; 91 out_deposit_fee := inner_rec.deposit_fee; 92 out_coin_pub := inner_rec.coin_pub; 93 RETURN NEXT; 94 remaining := remaining - 1; 95 EXIT WHEN remaining <= 0; 96 END LOOP; 97 EXCEPTION 98 WHEN undefined_table 99 THEN 100 NULL; 101 END; 102 END LOOP; 103 END 104 $FN$; 105 COMMENT ON FUNCTION merchant.lookup_pending_deposits(TEXT, INT8, INT8, BOOLEAN) 106 IS 'Returns up to p_limit pending-settlement deposit rows for the given exchange' 107 ' across all instance schemas, with merchant_priv read from' 108 ' merchant.merchant_instances (NULL if absent).';