refactor procedures.sql

1 files changed, 82 insertions, 0 deletions

diff --git a/src/exchangedb/exchange_do_recoup_by_reserve.sql b/src/exchangedb/exchange_do_recoup_by_reserve.sql
new file mode 100644
index 000000000..6a7ea725e
--- /dev/null
+++ b/src/exchangedb/exchange_do_recoup_by_reserve.sql
@@ -0,0 +1,82 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_recoup_by_reserve(
+  IN res_pub BYTEA
+)
+RETURNS TABLE
+(
+  denom_sig            BYTEA,
+  denominations_serial BIGINT,
+  coin_pub             BYTEA,
+  coin_sig             BYTEA,
+  coin_blind           BYTEA,
+  amount_val           BIGINT,
+  amount_frac          INTEGER,
+  recoup_timestamp     BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  res_uuid BIGINT;
+  blind_ev BYTEA;
+  c_pub    BYTEA;
+BEGIN
+  SELECT reserve_uuid
+  INTO res_uuid
+  FROM exchange.reserves
+  WHERE reserves.reserve_pub = res_pub;
+
+  FOR blind_ev IN
+    SELECT h_blind_ev
+      FROM exchange.reserves_out_by_reserve
+    WHERE reserves_out_by_reserve.reserve_uuid = res_uuid
+  LOOP
+    SELECT robr.coin_pub
+      INTO c_pub
+      FROM exchange.recoup_by_reserve robr
+    WHERE robr.reserve_out_serial_id = (
+      SELECT reserves_out.reserve_out_serial_id
+        FROM exchange.reserves_out
+      WHERE reserves_out.h_blind_ev = blind_ev
+    );
+    RETURN QUERY
+      SELECT kc.denom_sig,
+             kc.denominations_serial,
+             rc.coin_pub,
+             rc.coin_sig,
+             rc.coin_blind,
+             rc.amount_val,
+             rc.amount_frac,
+             rc.recoup_timestamp
+      FROM (
+        SELECT *
+        FROM exchange.known_coins
+        WHERE known_coins.coin_pub = c_pub
+      ) kc
+      JOIN (
+        SELECT *
+        FROM exchange.recoup
+        WHERE recoup.coin_pub = c_pub
+      ) rc USING (coin_pub);
+  END LOOP;
+END;
+$$;
+
+COMMENT ON FUNCTION exchange_do_recoup_by_reserve
+  IS 'Recoup by reserve as a function to make sure we hit only the needed partition and not all when joining as joins on distributed tables fetch ALL rows from the shards';
+