On Tue, Oct 7, 2014 at 8:38 AM, Ali Akbar <the.ap...@gmail.com> wrote:
> 2014-10-06 22:51 GMT+07:00 Marti Raudsepp <ma...@juffo.org>:
>
>
>
>> > the one that tests values just before numeric overflow
>>
>> Actually I don't know if that's too useful. I think you should add a
>> test case that causes an error to be thrown.
>>
>
> Actually i added the test case because in the code, when adding step into
> current for the last value, i expected it to overflow:
>
> /* increment current in preparation for next iteration */
> add_var(&fctx->current, &fctx->step, &fctx->current);
>
> where in the last calculation, current is 9 * 10^131071. Plus 10^131071,
> it will be 10^131072, which i expected to overflow numeric type (in the
> doc, numeric's range is "up to 131072 digits before the decimal point").
>
> In attached patch, i narrowed the test case to produce smaller result.
>
Well, as things stand now, the logic relies on cmp_var and the sign of the
stop and current values. it is right that it would be better to check for
overflow before going through the next iteration, and the cleanest and
cheapest way to do so would be to move the call to make_result after
add_var and save the result variable in the function context, or something
similar, but honestly I am not sure it is worth the complication as it
would mean some refactoring on what make_result actually already does.
I looked at this patch again a bit and finished with the attached, adding
an example in the docs, refining the error messages and improving a bit the
regression tests. I have nothing more to comment, so I am marking this
patch as "ready for committer".
Regards,
--
Michael
From 4f8591c47bb51ea86b77be645d9c321a1e9fb962 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@vmware.com>
Date: Tue, 14 Oct 2014 14:39:51 +0900
Subject: [PATCH] Implement generate_series for numeric data type
This commit adds two new system functions to generate series of numbers
for the data type numeric, with the possibility to define a custom step
value. Note that Nan is not accepted in output for all the input
variables.
---
doc/src/sgml/func.sgml | 16 +++--
src/backend/utils/adt/numeric.c | 124 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 4 ++
src/include/utils/builtins.h | 2 +
src/test/regress/expected/numeric.out | 52 ++++++++++++++
src/test/regress/sql/numeric.sql | 17 +++++
6 files changed, 211 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..b58cfa5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14076,8 +14076,8 @@ 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><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
@@ -14086,8 +14086,8 @@ 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><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>
@@ -14137,6 +14137,14 @@ SELECT * FROM generate_series(4,3);
-----------------
(0 rows)
+SELECT generate_series(1.1, 4, 1.3);
+ generate_series
+-----------------
+ 1.1
+ 2.4
+ 3.7
+(3 rows)
+
-- this example relies on the date-plus-integer operator
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 2d6a4cb..db2f862 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -28,6 +28,7 @@
#include "access/hash.h"
#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -261,6 +262,18 @@ typedef struct NumericVar
/* ----------
+ * Data for generate_series
+ * ----------
+ */
+typedef struct
+{
+ NumericVar current;
+ NumericVar stop;
+ NumericVar step;
+} generate_series_numeric_fctx;
+
+
+/* ----------
* Some preinitialized constants
* ----------
*/
@@ -1221,6 +1234,117 @@ numeric_floor(PG_FUNCTION_ARGS)
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 stop_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* handle NaN in start and stop values */
+ if (NUMERIC_IS_NAN(start_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start value cannot be NaN")));
+
+ if (NUMERIC_IS_NAN(stop_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stop value 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(stop_num, &fctx->stop);
+ 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 state as the result of this
+ * iteration.
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->stop) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->stop) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /* switch to memory context appropriate for iteration calculation */
+ 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().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 4736532..bcc02f6 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3923,6 +3923,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
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 = 3259 ( 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 = 3260 ( 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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index fb1b4a4..523cc79 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1029,6 +1029,8 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
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);
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 5fafdaf..ee6cb50 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -1409,3 +1409,55 @@ select 10.0 ^ 2147483647 as overflows;
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+--
+-- Tests for generate_series
+--
+select * from generate_series(0.0::numeric, 4.0::numeric);
+ generate_series
+-----------------
+ 0.0
+ 1.0
+ 2.0
+ 3.0
+ 4.0
+(5 rows)
+
+select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
+ generate_series
+-----------------
+ 0.1
+ 1.4
+ 2.7
+ 4.0
+(4 rows)
+
+select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
+ generate_series
+-----------------
+ 4.0
+ 1.8
+ -0.4
+(3 rows)
+
+-- Trigger errors
+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, 10::numeric);
+ERROR: start value cannot be NaN
+select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
+ERROR: stop value cannot be NaN
+-- Checks maximum, output is truncated
+select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(6 * (10::numeric ^ 131071),
+ 9 * (10::numeric ^ 131071),
+ 10::numeric ^ 131071) as a(i);
+ numeric
+---------
+ 6
+ 7
+ 8
+ 9
+(4 rows)
+
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 5c08717..a7e92ac 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -837,3 +837,20 @@ select 10.0 ^ -2147483648 as rounds_to_zero;
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+--
+-- Tests for generate_series
+--
+select * from generate_series(0.0::numeric, 4.0::numeric);
+select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
+select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
+-- Trigger errors
+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, 10::numeric);
+select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
+-- Checks maximum, output is truncated
+select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(6 * (10::numeric ^ 131071),
+ 9 * (10::numeric ^ 131071),
+ 10::numeric ^ 131071) as a(i);
--
2.1.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
