Skip to content

Commit b6d1559

Browse files
tglsfdctglsfdc
committed
Add timestamp and timestamptz versions of generate_series().
Hitoshi Harada
1 parent 600da67 commit b6d1559

File tree

5 files changed

+229
-15
lines changed

5 files changed

+229
-15
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 38 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.435 2008/05/04 21:13:35 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.436 2008/05/04 23:19:23 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10650,6 +10650,16 @@ AND
1065010650
</entry>
1065110651
</row>
1065210652

10653+
<row>
10654+
<entry><literal><function>generate_series</function>(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter> <type>interval</>)</literal></entry>
10655+
<entry><type>timestamp</type> or <type>timestamp with time zone</type></entry>
10656+
<entry><type>setof timestamp</type> or <type>setof timestamp with time zone</type> (same as argument type)</entry>
10657+
<entry>
10658+
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
10659+
with a step size of <parameter>step</parameter>
10660+
</entry>
10661+
</row>
10662+
1065310663
</tbody>
1065410664
</tgroup>
1065510665
</table>
@@ -10683,23 +10693,34 @@ select * from generate_series(4,3);
1068310693
-----------------
1068410694
(0 rows)
1068510695

10696+
-- this example relies on the date-plus-integer operator
1068610697
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
1068710698
dates
1068810699
------------
1068910700
2004-02-05
1069010701
2004-02-12
1069110702
2004-02-19
1069210703
(3 rows)
10704+
10705+
select * from generate_series('2008-03-01 00:00'::timestamp,
10706+
'2008-03-04 12:00', '10 hours');
10707+
generate_series
10708+
---------------------
10709+
2008-03-01 00:00:00
10710+
2008-03-01 10:00:00
10711+
2008-03-01 20:00:00
10712+
2008-03-02 06:00:00
10713+
2008-03-02 16:00:00
10714+
2008-03-03 02:00:00
10715+
2008-03-03 12:00:00
10716+
2008-03-03 22:00:00
10717+
2008-03-04 08:00:00
10718+
(9 rows)
1069310719
</programlisting>
1069410720
</para>
1069510721

1069610722
<table id="functions-srf-subscripts">
10697-
10698-
<indexterm>
10699-
<primary>generate_subscripts</primary>
10700-
</indexterm>
10701-
10702-
<title>Subscripts Generating Functions</title>
10723+
<title>Subscript Generating Functions</title>
1070310724
<tgroup cols="3">
1070410725
<thead>
1070510726
<row>
@@ -10711,15 +10732,15 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a);
1071110732

1071210733
<tbody>
1071310734
<row>
10714-
<entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>)</literal></entry>
10735+
<entry><literal><function>generate_subscripts</function>(<parameter>array anyarray</parameter>, <parameter>dim int</parameter>)</literal></entry>
1071510736
<entry><type>setof int</type></entry>
1071610737
<entry>
1071710738
Generate a series comprising the given array's subscripts.
1071810739
</entry>
1071910740
</row>
1072010741

1072110742
<row>
10722-
<entry><literal><function>generate_subscripts</function>(<parameter>array annyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry>
10743+
<entry><literal><function>generate_subscripts</function>(<parameter>array anyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry>
1072310744
<entry><type>setof int</type></entry>
1072410745
<entry>
1072510746
Generate a series comprising the given array's subscripts. When
@@ -10732,10 +10753,17 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a);
1073210753
</tgroup>
1073310754
</table>
1073410755

10756+
<indexterm>
10757+
<primary>generate_subscripts</primary>
10758+
</indexterm>
10759+
1073510760
<para>
10761+
<function>generate_subscripts</> is a convenience function that generates
10762+
the set of valid subscripts for the specified dimension of the given
10763+
array.
1073610764
Zero rows are returned for arrays that do not have the requested dimension,
1073710765
or for NULL arrays (but valid subscripts are returned for NULL array
10738-
elements.) Some examples follow:
10766+
elements). Some examples follow:
1073910767
<programlisting>
1074010768
-- basic usage
1074110769
select generate_subscripts('{NULL,1,NULL,2}'::int[], 1) as s;

‎src/backend/utils/adt/timestamp.c

Lines changed: 180 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.188 2008/05/04 21:13:35 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.189 2008/05/04 23:19:23 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -24,6 +24,7 @@
2424
#include "access/hash.h"
2525
#include "access/xact.h"
2626
#include "catalog/pg_type.h"
27+
#include "funcapi.h"
2728
#include "libpq/pqformat.h"
2829
#include "miscadmin.h"
2930
#include "parser/scansup.h"
@@ -45,6 +46,22 @@ TimestampTz PgStartTime;
4546
/* Set at configuration reload */
4647
TimestampTz PgReloadTime;
4748

49+
typedef struct
50+
{
51+
Timestamp current;
52+
Timestamp finish;
53+
Interval step;
54+
int step_sign;
55+
} generate_series_timestamp_fctx;
56+
57+
typedef struct
58+
{
59+
TimestampTz current;
60+
TimestampTz finish;
61+
Interval step;
62+
int step_sign;
63+
} generate_series_timestamptz_fctx;
64+
4865

4966
static TimeOffset time2t(const int hour, const int min, const int sec, const fsec_t fsec);
5067
static int EncodeSpecialTimestamp(Timestamp dt, char *str);
@@ -4651,3 +4668,165 @@ timestamptz_izone(PG_FUNCTION_ARGS)
46514668

46524669
PG_RETURN_TIMESTAMP(result);
46534670
}
4671+
4672+
/* generate_series_timestamp()
4673+
* Generate the set of timestamps from start to finish by step
4674+
*/
4675+
Datum
4676+
generate_series_timestamp(PG_FUNCTION_ARGS)
4677+
{
4678+
FuncCallContext *funcctx;
4679+
generate_series_timestamp_fctx *fctx;
4680+
Timestamp result;
4681+
4682+
/* stuff done only on the first call of the function */
4683+
if (SRF_IS_FIRSTCALL())
4684+
{
4685+
Timestamp start = PG_GETARG_TIMESTAMP(0);
4686+
Timestamp finish = PG_GETARG_TIMESTAMP(1);
4687+
Interval *step = PG_GETARG_INTERVAL_P(2);
4688+
MemoryContext oldcontext;
4689+
Interval interval_zero;
4690+
4691+
/* create a function context for cross-call persistence */
4692+
funcctx = SRF_FIRSTCALL_INIT();
4693+
4694+
/*
4695+
* switch to memory context appropriate for multiple function calls
4696+
*/
4697+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
4698+
4699+
/* allocate memory for user context */
4700+
fctx = (generate_series_timestamp_fctx *)
4701+
palloc(sizeof(generate_series_timestamp_fctx));
4702+
4703+
/*
4704+
* Use fctx to keep state from call to call. Seed current with the
4705+
* original start value
4706+
*/
4707+
fctx->current = start;
4708+
fctx->finish = finish;
4709+
fctx->step = *step;
4710+
4711+
/* Determine sign of the interval */
4712+
MemSet(&interval_zero, 0, sizeof(Interval));
4713+
fctx->step_sign = interval_cmp_internal(&fctx->step, &interval_zero);
4714+
4715+
if (fctx->step_sign == 0)
4716+
ereport(ERROR,
4717+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4718+
errmsg("step size cannot equal zero")));
4719+
4720+
funcctx->user_fctx = fctx;
4721+
MemoryContextSwitchTo(oldcontext);
4722+
}
4723+
4724+
/* stuff done on every call of the function */
4725+
funcctx = SRF_PERCALL_SETUP();
4726+
4727+
/*
4728+
* get the saved state and use current as the result for this iteration
4729+
*/
4730+
fctx = funcctx->user_fctx;
4731+
result = fctx->current;
4732+
4733+
if (fctx->step_sign > 0 ?
4734+
timestamp_cmp_internal(result, fctx->finish) <= 0 :
4735+
timestamp_cmp_internal(result, fctx->finish) >= 0)
4736+
{
4737+
/* increment current in preparation for next iteration */
4738+
fctx->current = DatumGetTimestamp(
4739+
DirectFunctionCall2(timestamp_pl_interval,
4740+
TimestampGetDatum(fctx->current),
4741+
PointerGetDatum(&fctx->step)));
4742+
4743+
/* do when there is more left to send */
4744+
SRF_RETURN_NEXT(funcctx, TimestampGetDatum(result));
4745+
}
4746+
else
4747+
{
4748+
/* do when there is no more left */
4749+
SRF_RETURN_DONE(funcctx);
4750+
}
4751+
}
4752+
4753+
/* generate_series_timestamptz()
4754+
* Generate the set of timestamps from start to finish by step
4755+
*/
4756+
Datum
4757+
generate_series_timestamptz(PG_FUNCTION_ARGS)
4758+
{
4759+
FuncCallContext *funcctx;
4760+
generate_series_timestamptz_fctx *fctx;
4761+
TimestampTz result;
4762+
4763+
/* stuff done only on the first call of the function */
4764+
if (SRF_IS_FIRSTCALL())
4765+
{
4766+
TimestampTz start = PG_GETARG_TIMESTAMPTZ(0);
4767+
TimestampTz finish = PG_GETARG_TIMESTAMPTZ(1);
4768+
Interval *step = PG_GETARG_INTERVAL_P(2);
4769+
MemoryContext oldcontext;
4770+
Interval interval_zero;
4771+
4772+
/* create a function context for cross-call persistence */
4773+
funcctx = SRF_FIRSTCALL_INIT();
4774+
4775+
/*
4776+
* switch to memory context appropriate for multiple function calls
4777+
*/
4778+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
4779+
4780+
/* allocate memory for user context */
4781+
fctx = (generate_series_timestamptz_fctx *)
4782+
palloc(sizeof(generate_series_timestamptz_fctx));
4783+
4784+
/*
4785+
* Use fctx to keep state from call to call. Seed current with the
4786+
* original start value
4787+
*/
4788+
fctx->current = start;
4789+
fctx->finish = finish;
4790+
fctx->step = *step;
4791+
4792+
/* Determine sign of the interval */
4793+
MemSet(&interval_zero, 0, sizeof(Interval));
4794+
fctx->step_sign = interval_cmp_internal(&fctx->step, &interval_zero);
4795+
4796+
if (fctx->step_sign == 0)
4797+
ereport(ERROR,
4798+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4799+
errmsg("step size cannot equal zero")));
4800+
4801+
funcctx->user_fctx = fctx;
4802+
MemoryContextSwitchTo(oldcontext);
4803+
}
4804+
4805+
/* stuff done on every call of the function */
4806+
funcctx = SRF_PERCALL_SETUP();
4807+
4808+
/*
4809+
* get the saved state and use current as the result for this iteration
4810+
*/
4811+
fctx = funcctx->user_fctx;
4812+
result = fctx->current;
4813+
4814+
if (fctx->step_sign > 0 ?
4815+
timestamp_cmp_internal(result, fctx->finish) <= 0 :
4816+
timestamp_cmp_internal(result, fctx->finish) >= 0)
4817+
{
4818+
/* increment current in preparation for next iteration */
4819+
fctx->current = DatumGetTimestampTz(
4820+
DirectFunctionCall2(timestamptz_pl_interval,
4821+
TimestampTzGetDatum(fctx->current),
4822+
PointerGetDatum(&fctx->step)));
4823+
4824+
/* do when there is more left to send */
4825+
SRF_RETURN_NEXT(funcctx, TimestampTzGetDatum(result));
4826+
}
4827+
else
4828+
{
4829+
/* do when there is no more left */
4830+
SRF_RETURN_DONE(funcctx);
4831+
}
4832+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.454 2008/05/04 21:13:35 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.455 2008/05/04 23:19:23 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200805041
56+
#define CATALOG_VERSION_NO 200805042
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.495 2008/05/04 21:13:36 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.496 2008/05/04 23:19:23 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3872,6 +3872,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 f f t t i 3 20 "
38723872
DESCR("non-persistent series generator");
38733873
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 f f t t i 2 20 "20 20" _null_ _null_ _null_ generate_series_int8 - _null_ _null_ ));
38743874
DESCR("non-persistent series generator");
3875+
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 f f t t i 3 1114 "1114 1114 1186" _null_ _null_ _null_ generate_series_timestamp - _null_ _null_ ));
3876+
DESCR("non-persistent series generator");
3877+
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 f f t t s 3 1184 "1184 1184 1186" _null_ _null_ _null_ generate_series_timestamptz - _null_ _null_ ));
3878+
DESCR("non-persistent series generator");
38753879

38763880
/* boolean aggregates */
38773881
DATA(insert OID = 2515 ( booland_statefunc PGNSP PGUID 12 1 0 f f t f i 2 16 "16 16" _null_ _null_ _null_ booland_statefunc - _null_ _null_ ));

‎src/include/utils/timestamp.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.77 2008/05/04 21:13:36 tgl Exp $
9+
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.78 2008/05/04 23:19:24 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -308,6 +308,9 @@ extern Datum clock_timestamp(PG_FUNCTION_ARGS);
308308
extern Datum pg_postmaster_start_time(PG_FUNCTION_ARGS);
309309
extern Datum pg_conf_load_time(PG_FUNCTION_ARGS);
310310

311+
extern Datum generate_series_timestamp(PG_FUNCTION_ARGS);
312+
extern Datum generate_series_timestamptz(PG_FUNCTION_ARGS);
313+
311314
/* Internal routines (not fmgr-callable) */
312315

313316
extern TimestampTz GetCurrentTimestamp(void);

0 commit comments

Comments
 (0)