SF.net SVN: ledger-smb:[3022] trunk

[..hidden..]

Revision: 3022
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3022&view=rev
Author:   einhverfr
Date:     2010-07-01 14:19:54 +0000 (Thu, 01 Jul 2010)

Log Message:
-----------
Performance fix for getting all years

Modified Paths:
--------------
    trunk/LedgerSMB/Form.pm
    trunk/sql/modules/Date.sql

Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm	2010-06-29 23:43:51 UTC (rev 3021)
+++ trunk/LedgerSMB/Form.pm	2010-07-01 14:19:54 UTC (rev 3022)
@@ -2221,8 +2221,7 @@
 
     # get years
     my $query = qq|
-	SELECT extract('YEARS' FROM transdate) FROM acc_trans 
-	GROUP BY extract('YEARS' FROM transdate) ORDER BY 1 DESC|;
+        SELECT * FROM date_get_all_years()|;
 
     my $sth = $dbh->prepare($query);
     $sth->execute();

Modified: trunk/sql/modules/Date.sql
===================================================================
--- trunk/sql/modules/Date.sql	2010-06-29 23:43:51 UTC (rev 3021)
+++ trunk/sql/modules/Date.sql	2010-07-01 14:19:54 UTC (rev 3022)
@@ -1,17 +1,25 @@
 CREATE OR REPLACE FUNCTION date_get_all_years() returns setof INT AS
 $$
-DECLARE
-    date_out record;
-    BEGIN
-        FOR date_out IN
-           SELECT EXTRACT('YEAR' from transdate) AS year
-           FROM acc_trans
-           GROUP BY EXTRACT('YEAR' from transdate)
-           ORDER BY year
-        LOOP
-             return next date_out.year;
-        END LOOP;
-    END;
+DECLARE next_record int;
+BEGIN
+
+SELECT MIN(EXTRACT ('YEAR' FROM transdate))::INT
+INTO next_record
+FROM acc_trans;
+
+LOOP
+
+  EXIT WHEN next_record IS NULL;
+  RETURN NEXT next_record;
+  SELECT MIN(EXTRACT ('YEAR' FROM transdate))::INT AS YEAR
+  INTO next_record
+  FROM acc_trans
+  WHERE EXTRACT ('YEAR' FROM transdate) > next_record;
+
+
+END LOOP;
+
+END;
 $$ language plpgsql;                                                                  
 COMMENT ON FUNCTION date_get_all_years() IS
 $$ This function return each year inside transdate in transactions. $$;


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