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()  (Neil Conway <neilc@samurai.com>)
Re: temporal variants of generate_series()  (David Fetter <david@fetter.org>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: TOASTing smaller things
Следующее
От: Neil Conway
Дата:
Сообщение: Re: [COMMITTERS] pgsql: RESET SESSION, plus related new DDL commands.