Re: [HACKERS] temporal variants of generate_series()

[Jim Nasby]

On Apr 28, 2007, at 8:00 PM, David Fetter wrote:
Here's an SQL version without much in the way of bounds checking :)
CREATE OR REPLACE FUNCTION generate_series (
    start_ts timestamptz,
    end_ts timestamptz,
    step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
    CASE
        WHEN $1 < $2 THEN
            $1
        WHEN $1 > $2 THEN
            $2
        END + s.i * $3 AS "generate_series"
FROM generate_series(
    0,
    floor(
        CASE
            WHEN $1 < $2 AND $3 > INTERVAL '0 seconds' THEN
                extract('epoch' FROM $2) -
                extract('epoch' FROM $1)
            WHEN $1 > $2 AND $3 < INTERVAL '0 seconds' THEN
                extract('epoch' FROM $1) -
                extract('epoch' FROM $2)
        END/extract('epoch' FROM $3)
    )::int8
) AS s(i);
$$;

It should be straight-forward to make similar ones to those below.

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).
--
Jim Nasby                                            [EMAIL PROTECTED]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org