On Thu, Apr 12, 2007 at 02:56:24PM -0700, Andrew Hammond wrote: > 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;
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. > CREATE OR REPLACE FUNCTION generate_series > ( start_ts date > , end_ts date > , step interval > ) RETURNS SETOF date > > -- time version > CREATE OR REPLACE FUNCTION generate_series > ( start_ts time > , end_ts time > , step interval > ) RETURNS SETOF time Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq