On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
> Jim Nasby <decibel@decibel.org> writes:
> > Are you sure the case statements are needed? It seems it would be
> > better to just punt to the behavior of generate_series (esp. if
> > generate_series eventually learns how to count backwards).
>
> What's this "eventually"?
>
> regression=# select * from generate_series(10,1,-1);
> generate_series
> -----------------
> 10
> 9
> 8
> 7
> 6
> 5
> 4
> 3
> 2
> 1
> (10 rows)
>
> regards, tom lane
Good point. I believe the function below does the right thing. When
given decreasing TIMESTAMPTZs and a negative interval, it will
generate them going backward in time. When given increasing
TIMESTAMPTZs and a positive interval, it will generate them going
forward in time. Given a 0 interval, it errors out, although not with
the same message as generate_series(1,1,0), and decreasing
TIMESTAMPTZs and a positive interval or vice versa, it generates no
rows.
CREATE OR REPLACE FUNCTION generate_series ( start_ts timestamptz, end_ts timestamptz, step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT $1 + s.i * $3 AS "generate_series"
FROM generate_series( CASE WHEN $1 <= $2 THEN 0 ELSE floor( ( extract('epoch'
FROM$2) - extract('epoch' FROM $1) )/extract('epoch' FROM $3) )::int8 END, CASE WHEN $1 <= $2
THENceil( ( extract('epoch' FROM $2) - extract('epoch' FROM $1) )/extract('epoch'
FROM$3) )::int8 ELSE 0 END, sign( extract('epoch' FROM $2) - extract('epoch' FROM $1)
)::int8
) AS s(i)
ORDER BY s.i ASC
;
$$;
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate