temporal variants of generate_series()
| От | Andrew Hammond |
|---|---|
| Тема | temporal variants of generate_series() |
| Дата | |
| Msg-id | 1176414984.881043.162250@p77g2000hsh.googlegroups.com обсуждение исходный текст |
| Ответы |
Re: temporal variants of generate_series()
Re: temporal variants of generate_series() |
| Список | pgsql-hackers |
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;
В списке pgsql-hackers по дате отправления: