[HACKERS] temporal variants of generate_series()

[Andrew Hammond]

I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.

-- timestamptz version
CREATE OR REPLACE FUNCTION generate_series
( start_ts timestamptz
, end_ts timestamptz
, step interval
) RETURNS SETOF timestamptz
AS $$
DECLARE
    current_ts timestamptz := start_ts;
BEGIN
IF start_ts < end_ts AND step > INTERVAL '0 seconds' THEN
    LOOP
        IF current_ts > end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
ELSIF end_ts < start_ts AND step < INTERVAL '0 seconds' THEN
    LOOP
        IF current_ts < end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


-- date version
CREATE OR REPLACE FUNCTION generate_series
( start_ts date
, end_ts date
, step interval
) RETURNS SETOF date
AS $$
DECLARE
    current_ts date := start_ts;
BEGIN
IF start_ts < end_ts AND step > INTERVAL '0 seconds' THEN
    LOOP
        IF current_ts > end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
ELSIF end_ts < start_ts AND step < INTERVAL '0 seconds' THEN
    LOOP
        IF current_ts < end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- time version
CREATE OR REPLACE FUNCTION generate_series
( start_ts time
, end_ts time
, step interval
) RETURNS SETOF time
AS $$
DECLARE
    current_ts time := start_ts;
BEGIN
IF step > INTERVAL '0 seconds' THEN
    LOOP    -- handle wraparound first
        IF current_ts < end_ts THEN
            EXIT;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
    LOOP
        IF current_ts > end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
ELSIF step < INTERVAL '0 seconds' THEN
    LOOP    -- handle wraparound first
        IF current_ts > end_ts THEN
            EXIT;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
    LOOP
        IF current_ts < end_ts THEN
            RETURN;
        END IF;
        RETURN NEXT current_ts;
        current_ts := current_ts + step;
    END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate