Thread: Generating dates prior to generate_series

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/

On Wed, 2007-04-04 at 07:00, Roger Tannous wrote:
> 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.

As someone who is still responsible for the care and feeding of a 7.4
database, thanks for the function.

You really should upgrade to the latest 7.3.xx version, as there were
many data loss bugs in earlier 7.3.xx versions that have been fixed.