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 <david@fetter.org> 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
