Lists: | pgsql-hackers |
---|
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 |
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;
From: | Neil Conway <neilc(at)samurai(dot)com> |
---|---|
To: | Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-04-25 15:58:21 |
Message-ID: | 1177516701.6440.4.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, 2007-04-12 at 14:56 -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.
I could see these being useful, but a PL/PgSQL implementation is not
eligible for inclusion in the core backend (since PL/PgSQL is not
enabled by default).
-Neil
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-04-28 19:00:44 |
Message-ID: | 20070428190044.GC18518@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)fetter(dot)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
From: | Jim Nasby <decibel(at)decibel(dot)org> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Cc: | Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-05-01 09:36:21 |
Message-ID: | 745BFDEB-76EA-4F02-B223-9E5C2A8DF549@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Apr 28, 2007, at 8:00 PM, David Fetter wrote:
> 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.
Are you sure the case statements are needed? It seems it would be
better to just punt to the behavior of generate_series (esp. if
generate_series eventually learns how to count backwards).
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jim Nasby <decibel(at)decibel(dot)org> |
Cc: | David Fetter <david(at)fetter(dot)org>, Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-05-01 21:08:45 |
Message-ID: | 406.1178053725@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Jim Nasby <decibel(at)decibel(dot)org> writes:
> Are you sure the case statements are needed? It seems it would be
> better to just punt to the behavior of generate_series (esp. if
> generate_series eventually learns how to count backwards).
What's this "eventually"?
regression=# select * from generate_series(10,1,-1);
generate_series
-----------------
10
9
8
7
6
5
4
3
2
1
(10 rows)
regards, tom lane
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jim Nasby <decibel(at)decibel(dot)org>, Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-05-01 22:05:30 |
Message-ID: | 20070501220530.GI31114@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)org> writes:
> > Are you sure the case statements are needed? It seems it would be
> > better to just punt to the behavior of generate_series (esp. if
> > generate_series eventually learns how to count backwards).
>
> What's this "eventually"?
>
> regression=# select * from generate_series(10,1,-1);
> generate_series
> -----------------
> 10
> 9
> 8
> 7
> 6
> 5
> 4
> 3
> 2
> 1
> (10 rows)
>
> regards, tom lane
Good point. I believe the function below does the right thing. When
given decreasing TIMESTAMPTZs and a negative interval, it will
generate them going backward in time. When given increasing
TIMESTAMPTZs and a positive interval, it will generate them going
forward in time. Given a 0 interval, it errors out, although not with
the same message as generate_series(1,1,0), and decreasing
TIMESTAMPTZs and a positive interval or vice versa, it generates no
rows.
CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
$1 + s.i * $3 AS "generate_series"
FROM generate_series(
CASE WHEN $1 <= $2
THEN 0
ELSE
floor(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
END,
CASE WHEN $1 <= $2
THEN ceil(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
ELSE
0
END,
sign(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)::int8
) AS s(i)
ORDER BY s.i ASC
;
$$;
--
David Fetter <david(at)fetter(dot)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