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

Commit1871c89

Browse files
committed
Add generate_series(numeric, numeric).
Платон МалюгинReviewed by Michael Paquier, Ali Akbar and Marti Raudsepp
1 parenta1b395b commit1871c89

File tree

7 files changed

+212
-5
lines changed

7 files changed

+212
-5
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -14076,8 +14076,8 @@ AND
1407614076
<tbody>
1407714077
<row>
1407814078
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
14079-
<entry><type>int</type>or <type>bigint</type></entry>
14080-
<entry><type>setof int</type>or <type>setofbigint</type> (same as argument type)</entry>
14079+
<entry><type>int</type>, <type>bigint</type>or <type>numeric</type></entry>
14080+
<entry><type>setof int</type>, <type>setof bigint</type>,or <type>setofnumeric</type> (same as argument type)</entry>
1408114081
<entry>
1408214082
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
1408314083
with a step size of one
@@ -14086,8 +14086,8 @@ AND
1408614086

1408714087
<row>
1408814088
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
14089-
<entry><type>int</type>or <type>bigint</type></entry>
14090-
<entry><type>setof int</type>or <type>setofbigint</type> (same as argument type)</entry>
14089+
<entry><type>int</type>, <type>bigint</type>or <type>numeric</type></entry>
14090+
<entry><type>setof int</type>, <type>setof bigint</type>or <type>setofnumeric</type> (same as argument type)</entry>
1409114091
<entry>
1409214092
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
1409314093
with a step size of <parameter>step</parameter>
@@ -14137,6 +14137,14 @@ SELECT * FROM generate_series(4,3);
1413714137
-----------------
1413814138
(0 rows)
1413914139

14140+
SELECT generate_series(1.1, 4, 1.3);
14141+
generate_series
14142+
-----------------
14143+
1.1
14144+
2.4
14145+
3.7
14146+
(3 rows)
14147+
1414014148
-- this example relies on the date-plus-integer operator
1414114149
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
1414214150
dates

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

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@
2828

2929
#include"access/hash.h"
3030
#include"catalog/pg_type.h"
31+
#include"funcapi.h"
3132
#include"libpq/pqformat.h"
3233
#include"miscadmin.h"
3334
#include"nodes/nodeFuncs.h"
@@ -260,6 +261,18 @@ typedef struct NumericVar
260261
}NumericVar;
261262

262263

264+
/* ----------
265+
* Data for generate_series
266+
* ----------
267+
*/
268+
typedefstruct
269+
{
270+
NumericVarcurrent;
271+
NumericVarstop;
272+
NumericVarstep;
273+
}generate_series_numeric_fctx;
274+
275+
263276
/* ----------
264277
* Some preinitialized constants
265278
* ----------
@@ -1229,6 +1242,117 @@ numeric_floor(PG_FUNCTION_ARGS)
12291242
PG_RETURN_NUMERIC(res);
12301243
}
12311244

1245+
1246+
/*
1247+
* generate_series_numeric() -
1248+
*
1249+
* Generate series of numeric.
1250+
*/
1251+
Datum
1252+
generate_series_numeric(PG_FUNCTION_ARGS)
1253+
{
1254+
returngenerate_series_step_numeric(fcinfo);
1255+
}
1256+
1257+
Datum
1258+
generate_series_step_numeric(PG_FUNCTION_ARGS)
1259+
{
1260+
generate_series_numeric_fctx*fctx;
1261+
FuncCallContext*funcctx;
1262+
MemoryContextoldcontext;
1263+
1264+
if (SRF_IS_FIRSTCALL())
1265+
{
1266+
Numericstart_num=PG_GETARG_NUMERIC(0);
1267+
Numericstop_num=PG_GETARG_NUMERIC(1);
1268+
NumericVarsteploc=const_one;
1269+
1270+
/* handle NaN in start and stop values */
1271+
if (NUMERIC_IS_NAN(start_num))
1272+
ereport(ERROR,
1273+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1274+
errmsg("start value cannot be NaN")));
1275+
1276+
if (NUMERIC_IS_NAN(stop_num))
1277+
ereport(ERROR,
1278+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1279+
errmsg("stop value cannot be NaN")));
1280+
1281+
/* see if we were given an explicit step size */
1282+
if (PG_NARGS()==3)
1283+
{
1284+
Numericstep_num=PG_GETARG_NUMERIC(2);
1285+
1286+
if (NUMERIC_IS_NAN(step_num))
1287+
ereport(ERROR,
1288+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1289+
errmsg("step size cannot be NaN")));
1290+
1291+
init_var_from_num(step_num,&steploc);
1292+
1293+
if (cmp_var(&steploc,&const_zero)==0)
1294+
ereport(ERROR,
1295+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1296+
errmsg("step size cannot equal zero")));
1297+
}
1298+
1299+
/* create a function context for cross-call persistence */
1300+
funcctx=SRF_FIRSTCALL_INIT();
1301+
1302+
/*
1303+
* Switch to memory context appropriate for multiple function calls.
1304+
*/
1305+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1306+
1307+
/* allocate memory for user context */
1308+
fctx= (generate_series_numeric_fctx*)
1309+
palloc(sizeof(generate_series_numeric_fctx));
1310+
1311+
/*
1312+
* Use fctx to keep state from call to call. Seed current with the
1313+
* original start value.
1314+
*/
1315+
init_var_from_num(start_num,&fctx->current);
1316+
init_var_from_num(stop_num,&fctx->stop);
1317+
init_var(&fctx->step);
1318+
set_var_from_var(&steploc,&fctx->step);
1319+
1320+
funcctx->user_fctx=fctx;
1321+
MemoryContextSwitchTo(oldcontext);
1322+
}
1323+
1324+
/* stuff done on every call of the function */
1325+
funcctx=SRF_PERCALL_SETUP();
1326+
1327+
/*
1328+
* Get the saved state and use current state as the result of this
1329+
* iteration.
1330+
*/
1331+
fctx=funcctx->user_fctx;
1332+
1333+
if ((fctx->step.sign==NUMERIC_POS&&
1334+
cmp_var(&fctx->current,&fctx->stop) <=0)||
1335+
(fctx->step.sign==NUMERIC_NEG&&
1336+
cmp_var(&fctx->current,&fctx->stop) >=0))
1337+
{
1338+
Numericresult=make_result(&fctx->current);
1339+
1340+
/* switch to memory context appropriate for iteration calculation */
1341+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1342+
1343+
/* increment current in preparation for next iteration */
1344+
add_var(&fctx->current,&fctx->step,&fctx->current);
1345+
MemoryContextSwitchTo(oldcontext);
1346+
1347+
/* do when there is more left to send */
1348+
SRF_RETURN_NEXT(funcctx,NumericGetDatum(result));
1349+
}
1350+
else
1351+
/* do when there is no more left */
1352+
SRF_RETURN_DONE(funcctx);
1353+
}
1354+
1355+
12321356
/*
12331357
* Implements the numeric version of the width_bucket() function
12341358
* defined by SQL2003. See also width_bucket_float8().

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201411071
56+
#defineCATALOG_VERSION_NO201411111
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3952,6 +3952,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
39523952
DESCR("non-persistent series generator");
39533953
DATA(insertOID=1069 (generate_seriesPGNSPPGUID121100000fffftti2020"20 20"_null__null__null__null_generate_series_int8_null__null__null_ ));
39543954
DESCR("non-persistent series generator");
3955+
DATA(insertOID=3259 (generate_seriesPGNSPPGUID121100000fffftti301700"1700 1700 1700"_null__null__null__null_generate_series_step_numeric_null__null__null_ ));
3956+
DESCR("non-persistent series generator");
3957+
DATA(insertOID=3260 (generate_seriesPGNSPPGUID121100000fffftti201700"1700 1700"_null__null__null__null_generate_series_numeric_null__null__null_ ));
3958+
DESCR("non-persistent series generator");
39553959
DATA(insertOID=938 (generate_seriesPGNSPPGUID121100000fffftti301114"1114 1114 1186"_null__null__null__null_generate_series_timestamp_null__null__null_ ));
39563960
DESCR("non-persistent series generator");
39573961
DATA(insertOID=939 (generate_seriesPGNSPPGUID121100000fffftts301184"1184 1184 1186"_null__null__null__null_generate_series_timestamptz_null__null__null_ ));

‎src/include/utils/builtins.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1029,6 +1029,8 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
10291029
externDatumint2int4_sum(PG_FUNCTION_ARGS);
10301030
externDatumwidth_bucket_numeric(PG_FUNCTION_ARGS);
10311031
externDatumhash_numeric(PG_FUNCTION_ARGS);
1032+
externDatumgenerate_series_numeric(PG_FUNCTION_ARGS);
1033+
externDatumgenerate_series_step_numeric(PG_FUNCTION_ARGS);
10321034

10331035
/* ri_triggers.c */
10341036
externDatumRI_FKey_check_ins(PG_FUNCTION_ARGS);

‎src/test/regress/expected/numeric.out

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1409,3 +1409,55 @@ select 10.0 ^ 2147483647 as overflows;
14091409
ERROR: value overflows numeric format
14101410
select 117743296169.0 ^ 1000000000 as overflows;
14111411
ERROR: value overflows numeric format
1412+
--
1413+
-- Tests for generate_series
1414+
--
1415+
select * from generate_series(0.0::numeric, 4.0::numeric);
1416+
generate_series
1417+
-----------------
1418+
0.0
1419+
1.0
1420+
2.0
1421+
3.0
1422+
4.0
1423+
(5 rows)
1424+
1425+
select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
1426+
generate_series
1427+
-----------------
1428+
0.1
1429+
1.4
1430+
2.7
1431+
4.0
1432+
(4 rows)
1433+
1434+
select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
1435+
generate_series
1436+
-----------------
1437+
4.0
1438+
1.8
1439+
-0.4
1440+
(3 rows)
1441+
1442+
-- Trigger errors
1443+
select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
1444+
ERROR: step size cannot equal zero
1445+
select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
1446+
ERROR: step size cannot be NaN
1447+
select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
1448+
ERROR: start value cannot be NaN
1449+
select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
1450+
ERROR: stop value cannot be NaN
1451+
-- Checks maximum, output is truncated
1452+
select (i / (10::numeric ^ 131071))::numeric(1,0)
1453+
from generate_series(6 * (10::numeric ^ 131071),
1454+
9 * (10::numeric ^ 131071),
1455+
10::numeric ^ 131071) as a(i);
1456+
numeric
1457+
---------
1458+
6
1459+
7
1460+
8
1461+
9
1462+
(4 rows)
1463+

‎src/test/regress/sql/numeric.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -837,3 +837,20 @@ select 10.0 ^ -2147483648 as rounds_to_zero;
837837
select10.0 ^-2147483647as rounds_to_zero;
838838
select10.0 ^2147483647as overflows;
839839
select117743296169.0 ^1000000000as overflows;
840+
841+
--
842+
-- Tests for generate_series
843+
--
844+
select*from generate_series(0.0::numeric,4.0::numeric);
845+
select*from generate_series(0.1::numeric,4.0::numeric,1.3::numeric);
846+
select*from generate_series(4.0::numeric,-1.5::numeric,-2.2::numeric);
847+
-- Trigger errors
848+
select*from generate_series(-100::numeric,100::numeric,0::numeric);
849+
select*from generate_series(-100::numeric,100::numeric,'nan'::numeric);
850+
select*from generate_series('nan'::numeric,100::numeric,10::numeric);
851+
select*from generate_series(0::numeric,'nan'::numeric,10::numeric);
852+
-- Checks maximum, output is truncated
853+
select (i/ (10::numeric ^131071))::numeric(1,0)
854+
from generate_series(6* (10::numeric ^131071),
855+
9* (10::numeric ^131071),
856+
10::numeric ^131071)as a(i);

0 commit comments

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