On Thu, Mar 10, 2016 at 10:58 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Thu, Mar 10, 2016 at 10:30 AM, Simon Riggs <si...@2ndquadrant.com>
> wrote:
> > On 10 March 2016 at 06:53, Michael Paquier <michael.paqu...@gmail.com>
> > wrote:
> >>
> >> On Wed, Mar 9, 2016 at 12:13 AM, Alvaro Herrera
> >> <alvhe...@2ndquadrant.com> wrote:
> >> > Robert Haas wrote:
> >> >> I'm pretty meh about the whole idea of this function, though,
> >> >> actually, and I don't see a single clear +1 vote for this
> >> >> functionality upthread. (Apologies if I've missed one.) In the
> >> >> absence of a few of those, I recommend we reject this.
> >> >
> >> > +1
> >>
> >> I'm meh for this patch.
> >
> >
> > "meh" == +1
> >
> > I thought it meant -1
>
> In my case it meant, like, -0.5. I don't really like adding lots of
> utility functions like this to the default install, because I'm not
> sure how widely they get used and it gradually increases the size of
> the code, system catalogs, etc. But I also don't want to block
> genuinely useful things. So basically, I'm not excited about this
> patch, but I don't want to fight about it either.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
New patch for Alvaro's consideration.
Very minor changes since the last time, the explanations below are
literally longer than the changes:
- Rebased, though I don't think any of the files had changed in the mean
time
- Removed infinity checks/errors and the test cases to match
- Amended documentation to add 'days' after 'step' as suggested
- Did not add a period as suggested, to remain consistent with other
descriptions in the same sgml table
- Altered test case and documentation of 7 day step example so that the
generated dates do not land evenly on the end date. A reader might
incorrectly infer that the end date must be in the result set, when it
doesn't have to be.
- Removed unnecessary indentation that existed purely due to following of
other generate_series implementations
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4b5ee81..0a8c280 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14700,6 +14700,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> days
+ </entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -14764,6 +14784,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-02'::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..af4000d 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,92 @@ timetz_izone(PG_FUNCTION_ARGS)
PG_RETURN_TIMETZADT_P(result);
}
+
+/* Corey BEGIN */
+typedef struct
+{
+ DateADT current;
+ DateADT stop;
+ int32 step;
+} generate_series_date_fctx;
+
+
+/* generate_series_date()
+ * Generate the set of dates from start to stop 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 stop = PG_GETARG_DATEADT(1);
+ int32 step = 1;
+ MemoryContext oldcontext;
+
+ /* 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")));
+ }
+
+ /* 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->stop = stop;
+ 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->stop) ||
+ (fctx->step < 0 && fctx->current >= fctx->stop))
+ {
+ /* 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));
+ }
+ /* 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 a0f821a..2adb50a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4033,6 +4033,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..9568224 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1452,3 +1452,32 @@ 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-02'::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)
+
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index e40b4c4..ff9a9d2 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -340,3 +340,8 @@ 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-02'::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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
