Re: using generate_series to iterate through months

On Mon, Aug 03, 2009 at 11:15:25AM -0400, Bill Reynolds wrote:
> Here is what I am using in the from clause (along with other tables) to
> generate the series of numbers for the number of months.  This seems to
> work:
> generate_series( 0, ((extract(years from age(current_date,
> DATE('2008-05-01')))*12) + extract(month from age(current_date,
> DATE('2008-05-01'))))::INTEGER) as s(a)

I doubt you're using it, but the generate_series in 8.4 knows how to
handle dates and intervals, for example you can do:

  SELECT generate_series(timestamp '2001-1-1','2004-1-1',interval '1 month');

to go from 2001 to 2004 in one month steps.  If not, I'd be tempted to
bung the above into a function at that does the same.  Something like
this should work OK for series with only a few thousand rows, but don't
use it to generate a microsecond spaced series covering several years:

  CREATE FUNCTION generate_series(timestamp,timestamp,interval)
      RETURNS SETOF timestamp
      LANGUAGE plpgsql
      IMMUTABLE AS $$
    DECLARE
      _c timestamp := $1;
    BEGIN
      WHILE _c < $2 LOOP
        RETURN NEXT _c;
    _c := _c + $3;
      END LOOP;
    END;
  $$;

--
  Sam  http://samason.me.uk/