Re: temporal variants of generate_series()

On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)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
THEN ceil(
(
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(at)fetter(dot)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