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.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9c143b2..15ebe47 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14657,6 +14657,26 @@ AND
</entry>
</row>
+ <row>
+ <entry><literal><function>generate_series(<parameter>start</parameter>,
<parameter>stop</parameter>)</function></literal></entry>
+ <entry><type>date</type></entry>
+ <entry><type>setof date</type></entry>
+ <entry>
+ Generate a series of values, from <parameter>start</parameter> to
<parameter>stop</parameter>
+ with a step size of one day
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal><function>generate_series(<parameter>start</parameter>,
<parameter>stop</parameter>, <parameter>step
integer</parameter>)</function></literal></entry>
+ <entry><type>date</type></entry>
+ <entry><type>setof date</type></entry>
+ <entry>
+ Generate a series of values, from <parameter>start</parameter> to
<parameter>stop</parameter>
+ with a step size of <parameter>step</parameter>
+ </entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -14721,6 +14741,26 @@ SELECT * FROM generate_series('2008-03-01
00:00'::timestamp,
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)
+
+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)
</programlisting>
</para>
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 332db7e..7404a2f 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -30,6 +30,7 @@
#include "utils/datetime.h"
#include "utils/nabstime.h"
#include "utils/sortsupport.h"
+#include "funcapi.h"
/*
* gcc's -ffast-math switch breaks routines that expect exact results from
@@ -2811,3 +2812,97 @@ timetz_izone(PG_FUNCTION_ARGS)
PG_RETURN_TIMETZADT_P(result);
}
+
+/* Corey BEGIN */
+typedef struct
+{
+ DateADT current;
+ DateADT finish;
+ int32 step;
+} generate_series_date_fctx;
+
+
+/* generate_series_date()
+ * Generate the set of dates from start to finish by step
+ */
+Datum
+generate_series_date(PG_FUNCTION_ARGS)
+{
+ return generate_series_step_date(fcinfo);
+}
+
+Datum
+generate_series_step_date(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ generate_series_date_fctx *fctx;
+ DateADT result;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ DateADT start = PG_GETARG_DATEADT(0);
+ DateADT finish = PG_GETARG_DATEADT(1);
+ int32 step = 1;
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ step = PG_GETARG_INT32(2);
+ if (step == 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal
zero")));
+
+ MemoryContext oldcontext;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
calls
+ */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_date_fctx *)
+ palloc(sizeof(generate_series_date_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with
the
+ * original start value
+ */
+ fctx->current = start;
+ fctx->finish = finish;
+ /* cannot iterate infinity */
+ if (DATE_NOT_FINITE(start) || DATE_NOT_FINITE(finish))
+ fctx->step = 0;
+ else
+ fctx->step = step;
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+ result = fctx->current;
+
+ if ((fctx->step > 0 && fctx->current <= fctx->finish) ||
+ (fctx->step < 0 && fctx->current >= fctx->finish))
+ {
+ /* increment current in preparation for next iteration */
+ fctx->current += fctx->step;
+
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, DateADTGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+}
+
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 79e92ff..65293eb 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4013,6 +4013,11 @@ DATA(insert OID = 938 ( generate_series PGNSP PGUID 12
1 1000 0 0 f f f f t t
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t
t s s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ _null_
generate_series_timestamptz _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+DATA(insert OID = 2739 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t
t s s 3 0 1082 "1082 1082 23" _null_ _null_ "{start,finish,step}" _null_ _null_
generate_series_step_date _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
+DATA(insert OID = 2740 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t
t s s 2 0 1082 "1082 1082" _null_ _null_ "{start,finish}" _null_ _null_
generate_series_date _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
+
/* boolean aggregates */
DATA(insert OID = 2515 ( booland_statefunc PGNSP PGUID
12 1 0 0 0 f f f f t f i s 2 0 16 "16 16" _null_ _null_ _null_ _null_ _null_
booland_statefunc _null_ _null_ _null_ ));
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index 1b962af..f427cde 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -205,4 +205,7 @@ extern Datum timetz_izone(PG_FUNCTION_ARGS);
extern Datum timetz_pl_interval(PG_FUNCTION_ARGS);
extern Datum timetz_mi_interval(PG_FUNCTION_ARGS);
+extern Datum generate_series_date(PG_FUNCTION_ARGS);
+extern Datum generate_series_step_date(PG_FUNCTION_ARGS);
+
#endif /* DATE_H */
diff --git a/src/test/regress/expected/date.out
b/src/test/regress/expected/date.out
index 56c5520..427f3df 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1452,3 +1452,42 @@ select make_time(10, 55, 100.1);
ERROR: time field value out of range: 10:55:100.1
select make_time(24, 0, 2.1);
ERROR: time field value out of range: 24:00:2.1
+SET datestyle TO iso;
+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)
+
+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)
+
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index e40b4c4..b790e32 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -340,3 +340,10 @@ select make_date(2013, 11, -1);
select make_date(-44, 3, 15); -- perhaps we should allow this sometime?
select make_time(10, 55, 100.1);
select make_time(24, 0, 2.1);
+
+SET datestyle TO iso;
+SELECT d.date_val FROM generate_series('1991-09-24'::date,'1991-10-01'::date)
as d(date_val);
+SELECT d.date_val FROM
generate_series('1991-09-24'::date,'1991-10-01'::date,7) as d(date_val);
+SELECT d.date_val FROM
generate_series('1999-12-31'::date,'1999-12-29'::date,-1) as d(date_val);
+SELECT d.date_val FROM generate_series('-infinity'::date,'1999-12-29'::date)
as d(date_val);
+SELECT d.date_val FROM generate_series('1991-09-24'::date,'infinity'::date) as
d(date_val);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
