insert_receipts_submitted.sql (2777B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024 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 do_insert_submitted_receipts; 18 CREATE FUNCTION do_insert_submitted_receipts( 19 IN in_h_tax_number BYTEA, 20 IN ina_h_donation_unit_pubs BYTEA[], 21 IN ina_nonces BYTEA[], 22 IN ina_donation_unit_sigs BYTEA[], 23 IN in_donation_year INT8, 24 -- 25 OUT out_conflict BOOL[], 26 OUT out_unknown_du INT4 27 ) 28 LANGUAGE plpgsql 29 AS $$ 30 DECLARE 31 i INT4; 32 ini_nonce BYTEA; 33 ini_h_donation_unit_pub BYTEA; 34 ini_donation_unit_sig BYTEA; 35 BEGIN 36 37 -- First pass: h_donation_unit_pub is client-supplied and is a foreign key 38 -- into donation_units. 'ON CONFLICT DO NOTHING' below absorbs unique 39 -- violations only, so an unknown donation unit would escape as SQLSTATE 40 -- 23503 -> HARD_ERROR -> HTTP 500, where 404 belongs. Reject the whole 41 -- batch up front, before anything has been written. 42 43 -- out_unknown_du is 0 if every unit is known, otherwise the 1-based index 44 -- of the first unknown one. 45 out_unknown_du = 0; 46 FOR i IN 1..array_length(ina_h_donation_unit_pubs,1) 47 LOOP 48 out_conflict[i] = FALSE; 49 IF (out_unknown_du = 0) 50 THEN 51 PERFORM FROM donation_units 52 WHERE h_donation_unit_pub=ina_h_donation_unit_pubs[i]; 53 IF NOT FOUND 54 THEN 55 out_unknown_du = i; 56 END IF; 57 END IF; 58 END LOOP; 59 60 IF (out_unknown_du > 0) 61 THEN 62 RETURN; 63 END IF; 64 65 -- Insert each donation receipt 66 67 FOR i IN 1..array_length(ina_h_donation_unit_pubs,1) 68 LOOP 69 ini_nonce = ina_nonces[i]; 70 ini_h_donation_unit_pub = ina_h_donation_unit_pubs[i]; 71 ini_donation_unit_sig = ina_donation_unit_sigs[i]; 72 73 INSERT INTO receipts_submitted 74 (h_tax_number 75 ,nonce 76 ,h_donation_unit_pub 77 ,donation_unit_sig 78 ,donation_year 79 ) 80 VALUES 81 (in_h_tax_number 82 ,ini_nonce 83 ,ini_h_donation_unit_pub 84 ,ini_donation_unit_sig 85 ,in_donation_year 86 ) 87 ON CONFLICT DO NOTHING; 88 IF NOT FOUND 89 THEN 90 PERFORM FROM receipts_submitted 91 WHERE h_tax_number=in_h_tax_number 92 AND donation_unit_sig=ini_donation_unit_sig; -- if signature matches, everything must match 93 out_conflict[i] = NOT FOUND; 94 END IF; 95 END LOOP; -- end FOR all receipts 96 97 END $$;