On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
> Jim Nasby <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
