From: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Add generate_series(date, date) and generate_series(date, date, integer) |
Date: | 2016-01-25 06:00:15 |
Message-ID: | CADkLM=dzw0Pvdqp5yWKxMd+VmNkAMhG=4ku7GnCZxebWnzmz3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
This patch addresses a personal need: nearly every time I use
generate_series for timestamps, I end up casting the result into date or
the ISO string thereof. Like such:
SELECT d.dt::date as dt
FROM generate_series('2015-01-01'::date,
'2016-01-04'::date,
interval '1 day') AS d(dt);
That's less than elegant.
With this patch, we can do this:
SELECT d.date_val FROM
generate_series('1991-09-24'::date,'1991-10-01'::date) as d(date_val);
date_val
------------
1991-09-24
1991-09-25
1991-09-26
1991-09-27
1991-09-28
1991-09-29
1991-09-30
1991-10-01
(8 rows)
SELECT d.date_val FROM
generate_series('1991-09-24'::date,'1991-10-01'::date,7) as d(date_val);
date_val
------------
1991-09-24
1991-10-01
(2 rows)
SELECT d.date_val FROM
generate_series('1999-12-31'::date,'1999-12-29'::date,-1) as d(date_val);
date_val
------------
1999-12-31
1999-12-30
1999-12-29
(3 rows)
One thing I discovered in doing this patch is that if you do a timestamp
generate_series involving infinity....it tries to do it. I didn't wait to
see if it finished.
For the date series, I put in checks to return an empty set:
SELECT d.date_val FROM
generate_series('-infinity'::date,'1999-12-29'::date) as d(date_val);
date_val
----------
(0 rows)
SELECT d.date_val FROM generate_series('1991-09-24'::date,'infinity'::date)
as d(date_val);
date_val
----------
(0 rows)
Notes:
- I borrowed the int4 implementation's check for step-size of 0 for POLA
reasons. However, it occurred to me that the function might be leakproof if
the behavior where changed to instead return an empty set. I'm not sure
that leakproof is a goal in and of itself.
First attempt at this patch attached. The examples above are copied from
the new test cases.
Attachment | Content-Type | Size |
---|---|---|
0001-add_generate_series_date.diff | text/plain | 8.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-01-25 06:12:07 | Re: Add generate_series(date, date) and generate_series(date, date, integer) |
Previous Message | Dilip Kumar | 2016-01-25 05:09:06 | Re: Patch: fix lock contention for HASHHDR.mutex |