temporal variants of generate_series()

From: "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: temporal variants of generate_series()
Date: 2007-04-12 21:56:24
Message-ID: 1176414984.881043.162250@p77g2000hsh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jason Lustig 2007-04-12 21:58:49 Re: Slow Postgresql server
Previous Message Scott Marlowe 2007-04-12 21:52:04 Re: Strangely Variable Query Performance