libeufin

commit 4c70adc49dd78381a42608bc1b1d68551f188d5f
parent b5caa5c6b150d31a453827e3c4b62fcf195996c0
Author: Antoine A <>
Date:   Mon,  6 Jan 2025 16:49:24 +0100

bank: rewrite statistic stored procedure to use static queries instead of dynamic ones

Diffstat:
M
database-versioning/libeufin-bank-procedures.sql
 | 
92
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------------------

1 file changed, 67 insertions(+), 25 deletions(-)
diff --git a/database-versioning/libeufin-bank-procedures.sql b/database-versioning/libeufin-bank-procedures.sql
@@ -1712,36 +1712,78 @@ CREATE PROCEDURE stats_register_payment(
   IN fiat_amount taler_amount
 )
 LANGUAGE plpgsql AS $$
-DECLARE
-  frame stat_timeframe_enum;
-  query TEXT;
 BEGIN
   IF now IS NULL THEN
     now = timezone('utc', now())::TIMESTAMP;
   END IF;
-  IF fiat_amount IS NULL THEN
-    query = format('INSERT INTO bank_stats AS s '
-      '(timeframe, start_time, %1$I_count, %1$I_volume) '
-      'VALUES ($1, $2, 1, $3) '
-      'ON CONFLICT (timeframe, start_time) DO UPDATE '
-      'SET %1$I_count=s.%1$I_count+1 '
-      ', %1$I_volume=(SELECT amount_add(s.%1$I_volume, $3))', 
-      name);
-    FOREACH frame IN ARRAY enum_range(null::stat_timeframe_enum) LOOP
-      EXECUTE query USING frame, date_trunc(frame::text, now), regional_amount;
-    END LOOP;
+  IF name = 'taler_in' THEN
+    INSERT INTO bank_stats AS s (
+      timeframe,
+      start_time,
+      taler_in_count,
+      taler_in_volume
+    ) SELECT
+      frame,
+      date_trunc(frame::text, now),
+      1,
+      regional_amount
+    FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
+    ON CONFLICT (timeframe, start_time) DO UPDATE 
+    SET taler_in_count=s.taler_in_count+1,
+        taler_in_volume=(SELECT amount_add(s.taler_in_volume, regional_amount));
+  ELSIF name = 'taler_out' THEN
+    INSERT INTO bank_stats AS s (
+      timeframe,
+      start_time,
+      taler_out_count,
+      taler_out_volume
+    ) SELECT
+      frame,
+      date_trunc(frame::text, now),
+      1,
+      regional_amount
+    FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
+    ON CONFLICT (timeframe, start_time) DO UPDATE 
+    SET taler_out_count=s.taler_out_count+1,
+        taler_out_volume=(SELECT amount_add(s.taler_out_volume, regional_amount));
+  ELSIF name = 'cashin' THEN
+    INSERT INTO bank_stats AS s (
+      timeframe,
+      start_time,
+      cashin_count,
+      cashin_regional_volume,
+      cashin_fiat_volume
+    ) SELECT
+      frame,
+      date_trunc(frame::text, now),
+      1,
+      regional_amount,
+      fiat_amount
+    FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame 
+    ON CONFLICT (timeframe, start_time) DO UPDATE 
+    SET cashin_count=s.cashin_count+1,
+        cashin_regional_volume=(SELECT amount_add(s.cashin_regional_volume, regional_amount)),
+        cashin_fiat_volume=(SELECT amount_add(s.cashin_fiat_volume, fiat_amount));
+  ELSIF name = 'cashout' THEN
+    INSERT INTO bank_stats AS s (
+      timeframe,
+      start_time,
+      cashout_count,
+      cashout_regional_volume,
+      cashout_fiat_volume
+    ) SELECT
+      frame,
+      date_trunc(frame::text, now),
+      1,
+      regional_amount,
+      fiat_amount
+    FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame 
+    ON CONFLICT (timeframe, start_time) DO UPDATE 
+    SET cashout_count=s.cashout_count+1,
+        cashout_regional_volume=(SELECT amount_add(s.cashout_regional_volume, regional_amount)),
+        cashout_fiat_volume=(SELECT amount_add(s.cashout_fiat_volume, fiat_amount));
   ELSE
-    query = format('INSERT INTO bank_stats AS s '
-      '(timeframe, start_time, %1$I_count, %1$I_regional_volume, %1$I_fiat_volume) '
-      'VALUES ($1, $2, 1, $3, $4)'
-      'ON CONFLICT (timeframe, start_time) DO UPDATE '
-      'SET %1$I_count=s.%1$I_count+1 '
-      ', %1$I_regional_volume=(SELECT amount_add(s.%1$I_regional_volume, $3))' 
-      ', %1$I_fiat_volume=(SELECT amount_add(s.%1$I_fiat_volume, $4))',
-      name);
-    FOREACH frame IN ARRAY enum_range(null::stat_timeframe_enum) LOOP
-      EXECUTE query USING frame, date_trunc(frame::text, now), regional_amount, fiat_amount;
-    END LOOP;
+    RAISE EXCEPTION 'Unknown stat %', name;
   END IF;
 END $$;