Thanks for the review. Attached the formatted patch according to your
suggestion.
- numeric datatype is large, but there are limitations. According to doc,
>> the limit is: up to 131072 digits before the decimal point; up to 16383
>> digits after the decimal point. How can we check if the next step
>> overflows? As a comparison, in int.c, generate_series_step_int4 checks if
>> its overflows, and stop the next call by setting step to 0. Should we do
>> that?
>>
> Yes we should.
>
how can we check the overflow after add_var?
(in int.c, the code checks for integer calculation overflow, that wraps the
result to negative value)
in numeric.sql regression test, i've added this query:
select (i / (10::numeric ^ 131071))::numeric(1,0)
from generate_series(-9*(10::numeric ^ 131071),
9*(10::numeric ^ 131071),
(10::numeric ^ 131071))
as a(i);
Because the doc notes that the maximum numeric digit before decimal point
is 131072, i hope this query covers the overflow case (in the last value it
will generate, if we add 9 x 10^13071 with 10^13071, add_var will
overflows). But in my tests, that isn't the case. The code works without
any error and returns the correct rows:
numeric
---------
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
(19 rows)
Regards,
--
Ali Akbar
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14074,14081 **** AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
--- 14074,14081 ----
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
***************
*** 14084,14091 **** AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
--- 14084,14091 ----
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 28,33 ****
--- 28,34 ----
#include "access/hash.h"
#include "catalog/pg_type.h"
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
***************
*** 261,266 **** typedef struct NumericVar
--- 262,279 ----
/* ----------
+ * Data for generate series
+ * ----------
+ */
+ typedef struct
+ {
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+ } generate_series_numeric_fctx;
+
+
+ /* ----------
* Some preinitialized constants
* ----------
*/
***************
*** 1221,1226 **** numeric_floor(PG_FUNCTION_ARGS)
--- 1234,1346 ----
PG_RETURN_NUMERIC(res);
}
+
+ /*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+ Datum
+ generate_series_numeric(PG_FUNCTION_ARGS)
+ {
+ return generate_series_step_numeric(fcinfo);
+ }
+
+ Datum
+ generate_series_step_numeric(PG_FUNCTION_ARGS)
+ {
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* Handle NaN in start & finish */
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start and finish cannot be NaN")));
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 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_numeric_fctx *) palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(finish_num, &fctx->finish);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->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;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->finish) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->finish) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+
+
+ MemoryContextSwitchTo(oldcontext);
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3923,3928 **** DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
--- 3923,3932 ----
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+ DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
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 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1040,1045 **** extern Datum int8_avg(PG_FUNCTION_ARGS);
--- 1040,1047 ----
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1409,1411 **** select 10.0 ^ 2147483647 as overflows;
--- 1409,1627 ----
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 1.1
+ 2.1
+ 3.1
+ 4.1
+ 5.1
+ 6.1
+ 7.1
+ 8.1
+ 9.1
+ (10 rows)
+
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 0.2
+ 0.3
+ 0.4
+ 0.5
+ 0.6
+ 0.7
+ 0.8
+ 0.9
+ 1.0
+ 1.1
+ 1.2
+ 1.3
+ 1.4
+ 1.5
+ 1.6
+ 1.7
+ 1.8
+ 1.9
+ 2.0
+ 2.1
+ 2.2
+ 2.3
+ 2.4
+ 2.5
+ 2.6
+ 2.7
+ 2.8
+ 2.9
+ 3.0
+ 3.1
+ 3.2
+ 3.3
+ 3.4
+ 3.5
+ 3.6
+ 3.7
+ 3.8
+ 3.9
+ 4.0
+ 4.1
+ 4.2
+ 4.3
+ 4.4
+ 4.5
+ 4.6
+ 4.7
+ 4.8
+ 4.9
+ 5.0
+ 5.1
+ 5.2
+ 5.3
+ 5.4
+ 5.5
+ 5.6
+ 5.7
+ 5.8
+ 5.9
+ 6.0
+ 6.1
+ 6.2
+ 6.3
+ 6.4
+ 6.5
+ 6.6
+ 6.7
+ 6.8
+ 6.9
+ 7.0
+ 7.1
+ 7.2
+ 7.3
+ 7.4
+ 7.5
+ 7.6
+ 7.7
+ 7.8
+ 7.9
+ 8.0
+ 8.1
+ 8.2
+ 8.3
+ 8.4
+ 8.5
+ 8.6
+ 8.7
+ 8.8
+ 8.9
+ 9.0
+ 9.1
+ 9.2
+ 9.3
+ 9.4
+ 9.5
+ 9.6
+ 9.7
+ 9.8
+ 9.9
+ 10.0
+ (100 rows)
+
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ generate_series
+ -----------------
+ 0.1
+ -0.15
+ -0.40
+ -0.65
+ -0.90
+ -1.15
+ -1.40
+ -1.65
+ -1.90
+ -2.15
+ -2.40
+ -2.65
+ -2.90
+ -3.15
+ -3.40
+ -3.65
+ -3.90
+ -4.15
+ -4.40
+ -4.65
+ -4.90
+ -5.15
+ -5.40
+ -5.65
+ -5.90
+ -6.15
+ -6.40
+ -6.65
+ -6.90
+ -7.15
+ -7.40
+ -7.65
+ -7.90
+ -8.15
+ -8.40
+ -8.65
+ -8.90
+ -9.15
+ -9.40
+ -9.65
+ -9.90
+ (41 rows)
+
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ ERROR: step size cannot equal zero
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ ERROR: step size cannot be NaN
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
+ numeric
+ ---------
+ -9
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ (19 rows)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 837,839 **** select 10.0 ^ -2147483648 as rounds_to_zero;
--- 837,859 ----
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
