SF.net SVN: ledger-smb:[3024] trunk/sql/modules/Payment.sql

[..hidden..]

Revision: 3024
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3024&view=rev
Author:   einhverfr
Date:     2010-07-02 21:49:21 +0000 (Fri, 02 Jul 2010)

Log Message:
-----------
Converting payments_get_open_currencies to loose index scan

Modified Paths:
--------------
    trunk/sql/modules/Payment.sql

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2010-07-01 20:01:35 UTC (rev 3023)
+++ trunk/sql/modules/Payment.sql	2010-07-02 21:49:21 UTC (rev 3024)
@@ -891,16 +891,25 @@
 CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)
 RETURNS SETOF char(3) AS
 $$
-DECLARE resultrow record;
+DECLARE result char(3);
 BEGIN
-        FOR resultrow IN
-          SELECT DISTINCT curr FROM ar
-          UNION
-          SELECT DISTINCT curr FROM ap 
-          ORDER BY curr
-          LOOP
-         return next resultrow.curr;
-        END LOOP;
+select min(curr) into result from ar WHERE in_account_class = 2
+union 
+select min(curr) from ap WHERE in_account_class = 1;
+
+
+LOOP
+   EXIT WHEN result IS NULL;
+   return next result;
+
+   SELECT min(curr) INTO result from ar 
+    where in_account_class = 2 and curr > result
+            union 
+   select min(curr) from ap 
+    WHERE in_account_class = 1 and curr > result
+    LIMIT 1;
+
+END LOOP;
 END;
 $$ language plpgsql;
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.