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/