DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=5RnusUf6hj23FKg+jfM7Vb4MHn2IQgiJAleLWi0oZQhxWkve3ZGRNcWs4TaaoIxS9uwlWj880s60V0eTe/PPbyqPyIj/+az/8RhwjYsRjNnA5wRkV6OaNFnFCZFKMRPbId5GDH3/cM1hY3BODZNneKtB9pN0b3iswCH1SYOfNwo=;
Date: Wed, 4 Apr 2007 05:00:56 -0700 (PDT)
From: Roger Tannous <roger77_lb@yahoo.com>
Subject: Generating dates prior to generate_series
To: PostgreSQL SQL Mailing List <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <580286.82124.qm@web90602.mail.mud.yahoo.com>

I'm using PostgreSQL version 7.3.2, and generate_series() is not
available, so this is a function to generate a series dates.

The function goes backwards if the second argument is less than the first
one. Check the two select statements at the end.

Best Regards, 
Roger Tannous.

CREATE FUNCTION date_range(VARCHAR, VARCHAR) RETURNS SETOF DATE AS '

   DECLARE

        dateStart ALIAS FOR $1;

        dateEnd ALIAS FOR $2;

                        
                        forwardSteps BOOLEAN := true;

                        
                        tmpDate DATE;

                        
   BEGIN

                        IF (to_date(dateStart, ''YYYY-mm-dd'') >
to_date(dateEnd, ''YYYY-mm-dd'')) THEN

                                    
                                    forwardSteps := false;

                                    
                        END IF;

                        
                        tmpDate := to_date(dateStart, ''YYYY-mm-dd'');

                        
                        WHILE ((forwardSteps AND tmpDate <=
to_date(dateEnd, ''YYYY-mm-dd'')) OR (NOT forwardSteps AND tmpDate >=
to_date(dateEnd, ''YYYY-mm-dd''))) LOOP

                    
                                    RETURN NEXT tmpDate;

                                    
                                    IF forwardSteps THEN 

                                    
                                                tmpDate := tmpDate +
interval ''1 day'';

                                    
                                    ELSE

                                    
                                                tmpDate := tmpDate -
interval ''1 day'';

                                    
                                    END IF;

 
                        END LOOP;

                        RETURN;

   END;

' LANGUAGE 'plpgsql';

 
select * FROM date_range('2007-01-03', '2007-03-20');

select * FROM date_range('2007-04-03', '2007-03-20');
 

____________________________________________________________________________________
Looking for earth-friendly autos? 
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/