On Fri, Oct 02, 2009 at 09:48:14PM +0000, Tim Landscheidt wrote:
> Sam Mason <sam@xxxxxxxxxxxxx> wrote:
> > 8.4 has a generate_series(timestamp,timestamp,interval) which would seem
> > to be a bit more flexible than you want.
>
> Yes, I know :-). But as "generate_series(A, B, C)" can also
> be written as "A + generate_series(0, (C - B) / C) * C" (or
> something "flexible" like that :-)), a
For things as complicated as timestamps I'm not sure if this is such a
trivial transform. If you can figure out the limit then it seems easy,
though I'm not sure how you'd do that.
> "generate_series(DATE, DATE)" would inter alia get rid off
> the need to cast the result from TIMESTAMP to DATE and to
> explicitly specify "'1 day'". Just a small, trivial enhance-
> ment for a popular use case :-).
Interesting, I tend to aim for maximum expressiveness not ease of
expressiveness. It would be somewhat easy to add the above if you want
though:
CREATE FUNCTION generate_series(date,date)
RETURNS SETOF date
IMMUTABLE LANGUAGE sql AS $$
SELECT generate_series($1::timestamp,$2::timestamp,interval '1 day')::date;
$$;
or I suppose you could use the integer series generation:
SELECT $1 + generate_series(0,$2 - $1);
Hum, now I'll have to see which is "better".
That second version seems to be slightly quicker (20 to 30%, for ranges
from a year up to a century respectively) so you may prefer it, but the
difference is going to be in the noise for any query I've ever used
generate_series for.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
