diff options
Diffstat (limited to 'src/exchangedb/exchange_do_batch2_reserves_in_insert.sql')
| -rw-r--r-- | src/exchangedb/exchange_do_batch2_reserves_in_insert.sql | 155 |
1 files changed, 64 insertions, 91 deletions
diff --git a/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql index 8d3942a6..6a0cc6d7 100644 --- a/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql +++ b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql @@ -26,6 +26,7 @@ CREATE OR REPLACE FUNCTION exchange_do_batch2_reserves_insert( IN in_payto_uri VARCHAR, IN in_reserve_expiration INT8, IN in_notify text, + IN in2_notify text, IN in2_reserve_pub BYTEA, IN in2_wire_ref INT8, IN in2_credit_val INT8, @@ -44,22 +45,29 @@ CREATE OR REPLACE FUNCTION exchange_do_batch2_reserves_insert( LANGUAGE plpgsql AS $$ DECLARE - curs_reserve_exist CURSOR - FOR SELECT reserve_pub - FROM reserves - WHERE ruuid = reserves.reserve_uuid OR ruuid2 = reserves.reserve_uuid; + curs_reserve_exist REFCURSOR; +-- FOR SELECT reserve_pub +-- FROM reserves +-- WHERE in_reserve_pub = reserves.reserve_pub +-- OR in2_reserve_pub = reserves.reserve_pub; +DECLARE curs_transaction_exist CURSOR FOR SELECT reserve_pub FROM reserves_in WHERE in_reserve_pub = reserves_in.reserve_pub - OR reserves_in.reserve_pub = in2_reserve_pub; - + OR in2_reserve_pub = reserves_in.reserve_pub; +DECLARE i RECORD; BEGIN - --SIMPLE INSERT ON CONFLICT DO NOTHING + transaction_duplicate=FALSE; + transaction_duplicate2=FALSE; + out_reserve_found = TRUE; + out_reserve_found2 = TRUE; + ruuid=0; + ruuid2=0; INSERT INTO wire_targets (wire_target_h_payto ,payto_uri) @@ -70,93 +78,65 @@ BEGIN ,in2_payto_uri) ON CONFLICT DO NOTHING; - FOR k IN curs_reserve_exist - LOOP - IF in_reserve_pub = k.reserve_pub + OPEN curs_reserve_exist FOR + WITH reserve_changes AS ( + INSERT INTO reserves + (reserve_pub + ,current_balance_val + ,current_balance_frac + ,expiration_date + ,gc_date) + VALUES + (in_reserve_pub + ,in_credit_val + ,in_credit_frac + ,in_expiration_date + ,in_gc_date), + (in2_reserve_pub + ,in2_credit_val + ,in2_credit_frac + ,in_expiration_date + ,in_gc_date) + ON CONFLICT DO NOTHING + RETURNING reserve_uuid,reserve_pub) + SELECT * FROM reserve_changes; + + FETCH FROM curs_reserve_exist INTO i; + IF FOUND + THEN + IF in_reserve_pub = i.reserve_pub THEN - out_reserve_found = TRUE; + out_reserve_found = FALSE; + ruuid = i.reserve_uuid; END IF; - - IF in2_reserve_pub = k.reserve_pub + IF in2_reserve_pub = i.reserve_pub THEN - out_reserve_found2 = TRUE; + out_reserve_found2 = FALSE; + ruuid2 = i.reserve_uuid; END IF; - - IF out_reserve_found AND out_reserve_found2 + FETCH FROM curs_reserve_exist INTO i; + IF FOUND THEN - EXIT; - END IF; - END LOOP; - - - IF out_reserve_found IS NULL - THEN - out_reserve_found=FALSE; - END IF; - IF out_reserve_found2 IS NULL - THEN + IF in_reserve_pub = i.reserve_pub + THEN + out_reserve_found = FALSE; + ruuid = i.reserve_uuid; + END IF; + IF in2_reserve_pub = i.reserve_pub + THEN out_reserve_found2 = FALSE; + ruuid2 = i.reserve_uuid; + END IF; + END IF; END IF; + CLOSE curs_reserve_exist; IF out_reserve_found AND out_reserve_found2 THEN - transaction_duplicate = FALSE; - transaction_duplicate2 = FALSE; - RETURN; + RETURN; END IF; - /*LOOP TO STORE UUID*/ - FOR i IN - WITH input_rows - (reserve_pub - ,current_balance_val - ,current_balance_frac - ,expiration_date - ,gc_date) - AS - ( - VALUES - (in_reserve_pub - ,in_credit_val - ,in_credit_frac - ,in_expiration_date - ,in_gc_date), - (in2_reserve_pub - ,in2_credit_val - ,in2_credit_frac - ,in_expiration_date - ,in_gc_date) - ), ins AS ( - INSERT INTO reserves - (reserve_pub - ,current_balance_val - ,current_balance_frac - ,expiration_date - ,gc_date) - SELECT * FROM input_rows - ON CONFLICT DO NOTHING - RETURNING reserve_uuid) - SELECT - * - FROM - ( - SELECT - reserve_uuid, - ROW_NUMBER () OVER (ORDER BY reserve_uuid) - FROM - ins - ) x - LOOP - IF i.ROW_NUMBER = 1 - THEN - ruuid = i.reserve_uuid; - ELSE - ruuid2 = i.reserve_uuid; - END IF; - END LOOP; - - - PERFORM pg_notify(in_notify, NULL); + PERFORM pg_notify(in2_notify, NULL); INSERT INTO reserves_in (reserve_pub @@ -207,15 +187,8 @@ BEGIN END LOOP; END IF; - IF transaction_duplicate IS NULL - THEN - transaction_duplicate=FALSE; - END IF; - IF transaction_duplicate2 IS NULL - THEN - transaction_duplicate2 = FALSE; - END IF; - + CLOSE curs_reserve_exist; + CLOSE curs_transaction_exist; RETURN; END $$; |