Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb6d1559

Browse files
committed
Add timestamp and timestamptz versions of generate_series().
Hitoshi Harada
1 parent600da67 commitb6d1559

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/0421:13:35 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.436 2008/05/0423: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>arrayannyarray</parameter>, <parameter>dim int</parameter>)</literal></entry>
10735+
<entry><literal><function>generate_subscripts</function>(<parameter>arrayanyarray</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>arrayannyarray</parameter>, <parameter>dim int</parameter>, <parameter>reverse boolean</parameter>)</literal></entry>
10743+
<entry><literal><function>generate_subscripts</function>(<parameter>arrayanyarray</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/0421:13:35 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.189 2008/05/0423: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
TimestampTzPgReloadTime;
4748

49+
typedefstruct
50+
{
51+
Timestampcurrent;
52+
Timestampfinish;
53+
Intervalstep;
54+
intstep_sign;
55+
}generate_series_timestamp_fctx;
56+
57+
typedefstruct
58+
{
59+
TimestampTzcurrent;
60+
TimestampTzfinish;
61+
Intervalstep;
62+
intstep_sign;
63+
}generate_series_timestamptz_fctx;
64+
4865

4966
staticTimeOffsettime2t(constinthour,constintmin,constintsec,constfsec_tfsec);
5067
staticintEncodeSpecialTimestamp(Timestampdt,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+
Timestampresult;
4681+
4682+
/* stuff done only on the first call of the function */
4683+
if (SRF_IS_FIRSTCALL())
4684+
{
4685+
Timestampstart=PG_GETARG_TIMESTAMP(0);
4686+
Timestampfinish=PG_GETARG_TIMESTAMP(1);
4687+
Interval*step=PG_GETARG_INTERVAL_P(2);
4688+
MemoryContextoldcontext;
4689+
Intervalinterval_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+
TimestampTzresult;
4762+
4763+
/* stuff done only on the first call of the function */
4764+
if (SRF_IS_FIRSTCALL())
4765+
{
4766+
TimestampTzstart=PG_GETARG_TIMESTAMPTZ(0);
4767+
TimestampTzfinish=PG_GETARG_TIMESTAMPTZ(1);
4768+
Interval*step=PG_GETARG_INTERVAL_P(2);
4769+
MemoryContextoldcontext;
4770+
Intervalinterval_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/0421:13:35 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.455 2008/05/0423:19:23 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200805041
56+
#defineCATALOG_VERSION_NO200805042
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/0421:13:36 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.496 2008/05/0423: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(insertOID=1069 (generate_seriesPGNSPPGUID1211000fftti220"20 20"_null__null__null_generate_series_int8-_null__null_ ));
38743874
DESCR("non-persistent series generator");
3875+
DATA(insertOID=938 (generate_seriesPGNSPPGUID1211000fftti31114"1114 1114 1186"_null__null__null_generate_series_timestamp-_null__null_ ));
3876+
DESCR("non-persistent series generator");
3877+
DATA(insertOID=939 (generate_seriesPGNSPPGUID1211000fftts31184"1184 1184 1186"_null__null__null_generate_series_timestamptz-_null__null_ ));
3878+
DESCR("non-persistent series generator");
38753879

38763880
/* boolean aggregates */
38773881
DATA(insertOID=2515 (booland_statefuncPGNSPPGUID1210fftfi216"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/0421:13:36 tgl Exp $
9+
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.78 2008/05/0423:19:24 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -308,6 +308,9 @@ extern Datum clock_timestamp(PG_FUNCTION_ARGS);
308308
externDatumpg_postmaster_start_time(PG_FUNCTION_ARGS);
309309
externDatumpg_conf_load_time(PG_FUNCTION_ARGS);
310310

311+
externDatumgenerate_series_timestamp(PG_FUNCTION_ARGS);
312+
externDatumgenerate_series_timestamptz(PG_FUNCTION_ARGS);
313+
311314
/* Internal routines (not fmgr-callable) */
312315

313316
externTimestampTzGetCurrentTimestamp(void);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp