Hello,
here is a patch implementing varwidth_bucket (naming is up for
discussion) function which does binning with variable bucket width. The
use-cases are same as for width_bucket (=data analytics, mainly
histograms), the difference is that width_bucket uses buckets of same
width but the varwidth_bucket accepts an sorted array of right-bound
thresholds to define the individual buckets.
Currently applications implement this with long CASE statements which
are quite hard to read/maintain and are much slower than this
implementation which uses binary search.
There are 3 actual functions, one generic and two faster versions for
the int8 and float8 input that take advantage of the static width of
those types.
The research leading to these results has received funding from the
European Union's Seventh Framework Programme (FP7/2007-2013) under grant
agreement n° 318633
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5c906f3..ceff2ae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -920,6 +920,38 @@
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
<entry><literal>3</literal></entry>
</row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>varwidth_bucket</primary>
+ </indexterm>
+ <literal><function>varwidth_bucket(<parameter>op</parameter> <type>anyelemnt</type>, <parameter>thresholds</parameter> <type>anyarray</type>)</function></literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>return the bucket to which <parameter>operand</> would
+ be assigned based on right-bound bucket <parameter>thresholds</>.</entry>
+ <entry><literal>varwidth_bucket(5.35::numeric, ARRAY[1, 3, 4, 6]::numeric)</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal><function>varwidth_bucket(<parameter>op</parameter> <type>dp</type>, <parameter>thresholds</parameter> <type>dp[]</type>)</function></literal></entry>
+ <entry><type>int</type></entry>
+ <entry>return the bucket to which <parameter>operand</> would
+ be assigned based on right-bound bucket <parameter>thresholds</>.</entry>
+ <entry><literal>varwidth_bucket(5.35, ARRAY[1, 3, 4, 6])</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal><function>varwidth_bucket(<parameter>op</parameter> <type>bigint</type>, <parameter>thresholds</parameter> <type>bigint[]</type>)</function></literal></entry>
+ <entry><type>int</type></entry>
+ <entry>return the bucket to which <parameter>operand</> would
+ be assigned based on right-bound bucket <parameter>thresholds</>.</entry>
+ <entry><literal>varwidth_bucket(5, ARRAY[1, 3, 4, 6])</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index f8e94ec..6f03206 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -5502,3 +5502,116 @@ array_replace(PG_FUNCTION_ARGS)
fcinfo);
PG_RETURN_ARRAYTYPE_P(array);
}
+
+/*
+ * Implements the generic version of the varwidth_bucket() function.
+ * See also varwidth_bucket_float8() and varwidth_bucket_int8().
+ *
+ * 'thresholds' is an array (must be sorted from smallest to biggest value)
+ * containing right-bounds for each "bucket", varwidth_bucket() returns
+ * integer indicating the bucket number that 'operand' belongs to. An operand
+ * greater than the upper bound is assigned to an additional bucket.
+ */
+Datum
+varwidth_bucket_generic(PG_FUNCTION_ARGS)
+{
+ Datum operand = PG_GETARG_DATUM(0);
+ ArrayType *thresholds_in = PG_GETARG_ARRAYTYPE_P(1);
+ char *thresholds;
+ Oid collation = PG_GET_COLLATION();
+ Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ TypeCacheEntry *typentry;
+ int typlen;
+ bool typbyval;
+ char typalign;
+ int32 left;
+ int32 mid;
+ int32 right;
+ FunctionCallInfoData locfcinfo;
+
+ /* Verify input */
+ if (ARR_NDIM(thresholds_in) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("thresholds must be one dimensional array ")));
+
+ if (ARR_HASNULL(thresholds_in))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("thresholds array must not contain NULLs")));
+
+ /* Make sure val and thresholds use same types so we can be sure they can be compared. */
+ if (element_type != ARR_ELEMTYPE(thresholds_in))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot calculate buckets for operand using thresholds of different type")));
+
+ /* Fetch information about the input type */
+ typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+ if (typentry == NULL ||
+ typentry->type_id != element_type)
+ {
+ typentry = lookup_type_cache(element_type,
+ TYPECACHE_CMP_PROC_FINFO);
+ if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify a comparison function for type %s",
+ format_type_be(element_type))));
+ fcinfo->flinfo->fn_extra = (void *) typentry;
+ }
+ typlen = typentry->typlen;
+ typbyval = typentry->typbyval;
+ typalign = typentry->typalign;
+
+ InitFunctionCallInfoData(locfcinfo, &typentry->cmp_proc_finfo, 2,
+ collation, NULL, NULL);
+
+ thresholds = (char *) ARR_DATA_PTR(thresholds_in);
+
+ /* Find the bucket */
+ left = 0;
+ right = ArrayGetNItems(ARR_NDIM(thresholds_in), ARR_DIMS(thresholds_in));
+ while (left < right) {
+ char *ptr = thresholds;
+ Datum elm;
+ int32 cmpresult;
+ int i;
+
+ mid = (left + right) / 2;
+
+ for (i = 0; i < mid-left; i++)
+ {
+ ptr = att_addlength_pointer(ptr, typlen, ptr);
+ ptr = (char *) att_align_nominal(ptr, typalign);
+ }
+
+ elm = fetch_att(ptr, typbyval, typlen);
+
+ locfcinfo.arg[0] = operand;
+ locfcinfo.arg[1] = elm;
+ locfcinfo.argnull[0] = false;
+ locfcinfo.argnull[1] = false;
+ locfcinfo.isnull = false;
+ cmpresult = DatumGetInt32(FunctionCallInvoke(&locfcinfo));
+
+ if (cmpresult < 0)
+ {
+ right = mid;
+ }
+ else
+ {
+ /* Move the thresholds pointer so we don't have to seek the
+ * beginning of array again */
+ thresholds = att_addlength_pointer(ptr, typlen, ptr);
+ thresholds = (char *) att_align_nominal(thresholds, typalign);
+
+ left = mid + 1;
+ }
+ }
+
+ /* Avoid leaking memory when handed toasted input. */
+ PG_FREE_IF_COPY(thresholds_in, 1);
+
+ PG_RETURN_INT32(left);
+}
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 41b3eaa..a1eaadf 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -2800,6 +2800,60 @@ width_bucket_float8(PG_FUNCTION_ARGS)
PG_RETURN_INT32(result);
}
+/*
+ * Implements the float8 version of the varwidth_bucket() function.
+ * See also varwidth_bucket_general() and varwidth_bucket_int8().
+ *
+ * 'thresholds' is an array (must be sorted from smallest to biggest value)
+ * containing right-bounds for each "bucket", varwidth_bucket() returns
+ * integer indicating the bucket number that 'operand' belongs to. An operand
+ * greater than the upper bound is assigned to an additional bucket.
+ */
+Datum
+varwidth_bucket_float8(PG_FUNCTION_ARGS)
+{
+ float8 operand = PG_GETARG_FLOAT8(0);
+ ArrayType *thresholds_in = PG_GETARG_ARRAYTYPE_P(1);
+ float8 *thresholds;
+ int32 left;
+ int32 mid;
+ int32 right;
+
+ /* Verify input */
+ if (ARR_NDIM(thresholds_in) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("thresholds must be one dimensional array ")));
+
+ if (ARR_HASNULL(thresholds_in))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("thresholds array must not contain NULLs")));
+
+ if (ARR_ELEMTYPE(thresholds_in) != FLOAT8OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
+ errmsg("thresholds array must be type float8[]")));
+
+ thresholds = (float8 *) ARR_DATA_PTR(thresholds_in);
+
+ /* Find the bucket */
+ left = 0;
+ right = ArrayGetNItems(ARR_NDIM(thresholds_in), ARR_DIMS(thresholds_in));
+ while (left < right) {
+ mid = (left + right) / 2;
+ if (operand < thresholds[mid])
+ right = mid;
+ else
+ left = mid + 1;
+ }
+
+ /* Avoid leaking memory when handed toasted input. */
+ PG_FREE_IF_COPY(thresholds_in, 1);
+
+ PG_RETURN_INT32(left);
+}
+
/* ========== PRIVATE ROUTINES ========== */
#ifndef HAVE_CBRT
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 96146e0..c1d3c21 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -17,8 +17,10 @@
#include <limits.h>
#include <math.h>
+#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
+#include "utils/array.h"
#include "utils/int8.h"
#include "utils/builtins.h"
@@ -1508,3 +1510,58 @@ generate_series_step_int8(PG_FUNCTION_ARGS)
/* do when there is no more left */
SRF_RETURN_DONE(funcctx);
}
+
+
+/*
+ * Implements the int8 version of the varwidth_bucket() function.
+ * See also varwidth_bucket_float8() and varwidth_bucket_generic().
+ *
+ * 'thresholds' is an array (must be sorted from smallest to biggest value)
+ * containing right-bounds for each "bucket", varwidth_bucket() returns
+ * integer indicating the bucket number that 'operand' belongs to. An operand
+ * greater than the upper bound is assigned to an additional bucket.
+ */
+Datum
+varwidth_bucket_int8(PG_FUNCTION_ARGS)
+{
+ int64 operand = PG_GETARG_INT64(0);
+ ArrayType *thresholds_in = PG_GETARG_ARRAYTYPE_P(1);
+ int64 *thresholds;
+ int32 left;
+ int32 mid;
+ int32 right;
+
+ /* Verify input */
+ if (ARR_NDIM(thresholds_in) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("thresholds must be one dimensional array ")));
+
+ if (ARR_HASNULL(thresholds_in))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("thresholds array must not contain NULLs")));
+
+ if (ARR_ELEMTYPE(thresholds_in) != INT8OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
+ errmsg("thresholds array must be type int8[]")));
+
+ thresholds = (int64 *) ARR_DATA_PTR(thresholds_in);
+
+ /* Find the bucket */
+ left = 0;
+ right = ArrayGetNItems(ARR_NDIM(thresholds_in), ARR_DIMS(thresholds_in));
+ while (left < right) {
+ mid = (left + right) / 2;
+ if (operand < thresholds[mid])
+ right = mid;
+ else
+ left = mid + 1;
+ }
+
+ /* Avoid leaking memory when handed toasted input. */
+ PG_FREE_IF_COPY(thresholds_in, 1);
+
+ PG_RETURN_INT32(left);
+}
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 19d0bdc..1608b72 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -35,6 +35,7 @@
#include "utils/builtins.h"
#include "utils/int8.h"
#include "utils/numeric.h"
+#include "utils/typcache.h"
/* ----------
* Uncomment the following to enable compilation of dump_numeric()
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 762ce6c..af7d6e9 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -515,6 +515,8 @@ DATA(insert OID = 309 ( float84gt PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0
DATA(insert OID = 310 ( float84ge PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "701 700" _null_ _null_ _null_ _null_ float84ge _null_ _null_ _null_ ));
DATA(insert OID = 320 ( width_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i 4 0 23 "701 701 701 23" _null_ _null_ _null_ _null_ width_bucket_float8 _null_ _null_ _null_ ));
DESCR("bucket number of operand in equidepth histogram");
+DATA(insert OID = 3255 ( varwidth_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "701 1022" _null_ _null_ _null_ _null_ varwidth_bucket_float8 _null_ _null_ _null_ ));
+DESCR("bucket number of operand in the set of right-bound buckets");
DATA(insert OID = 311 ( float8 PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 701 "700" _null_ _null_ _null_ _null_ ftod _null_ _null_ _null_ ));
DESCR("convert float4 to float8");
@@ -731,6 +733,8 @@ DATA(insert OID = 469 ( int8lt PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16
DATA(insert OID = 470 ( int8gt PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "20 20" _null_ _null_ _null_ _null_ int8gt _null_ _null_ _null_ ));
DATA(insert OID = 471 ( int8le PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "20 20" _null_ _null_ _null_ _null_ int8le _null_ _null_ _null_ ));
DATA(insert OID = 472 ( int8ge PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "20 20" _null_ _null_ _null_ _null_ int8ge _null_ _null_ _null_ ));
+DATA(insert OID = 3257 ( varwidth_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "20 1016" _null_ _null_ _null_ _null_ varwidth_bucket_int8 _null_ _null_ _null_ ));
+DESCR("bucket number of operand in the set of right-bound buckets");
DATA(insert OID = 474 ( int84eq PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "20 23" _null_ _null_ _null_ _null_ int84eq _null_ _null_ _null_ ));
DATA(insert OID = 475 ( int84ne PGNSP PGUID 12 1 0 0 0 f f f t t f i 2 0 16 "20 23" _null_ _null_ _null_ _null_ int84ne _null_ _null_ _null_ ));
@@ -889,6 +893,8 @@ DATA(insert OID = 3817 ( arraycontsel PGNSP PGUID 12 1 0 0 0 f f f f t f s 4
DESCR("restriction selectivity for array-containment operators");
DATA(insert OID = 3818 ( arraycontjoinsel PGNSP PGUID 12 1 0 0 0 f f f f t f s 5 0 701 "2281 26 2281 21 2281" _null_ _null_ _null_ _null_ arraycontjoinsel _null_ _null_ _null_ ));
DESCR("join selectivity for array-containment operators");
+DATA(insert OID = 3256 ( varwidth_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "2283 2277" _null_ _null_ _null_ _null_ varwidth_bucket_generic _null_ _null_ _null_ ));
+DESCR("bucket number of operand in the set of right-bound buckets");
DATA(insert OID = 760 ( smgrin PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 210 "2275" _null_ _null_ _null_ _null_ smgrin _null_ _null_ _null_ ));
DESCR("I/O");
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 9bbfaae..e0cf787 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -214,6 +214,7 @@ extern Datum array_fill_with_lower_bounds(PG_FUNCTION_ARGS);
extern Datum array_unnest(PG_FUNCTION_ARGS);
extern Datum array_remove(PG_FUNCTION_ARGS);
extern Datum array_replace(PG_FUNCTION_ARGS);
+extern Datum varwidth_bucket_generic(PG_FUNCTION_ARGS);
extern Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
int arraytyplen, int elmlen, bool elmbyval, char elmalign,
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index bbb5d39..77f3406 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -451,6 +451,7 @@ extern Datum float84le(PG_FUNCTION_ARGS);
extern Datum float84gt(PG_FUNCTION_ARGS);
extern Datum float84ge(PG_FUNCTION_ARGS);
extern Datum width_bucket_float8(PG_FUNCTION_ARGS);
+extern Datum varwidth_bucket_float8(PG_FUNCTION_ARGS);
/* dbsize.c */
extern Datum pg_tablespace_size_oid(PG_FUNCTION_ARGS);
diff --git a/src/include/utils/int8.h b/src/include/utils/int8.h
index 0e4b949..b4c00a0 100644
--- a/src/include/utils/int8.h
+++ b/src/include/utils/int8.h
@@ -126,4 +126,6 @@ extern Datum oidtoi8(PG_FUNCTION_ARGS);
extern Datum generate_series_int8(PG_FUNCTION_ARGS);
extern Datum generate_series_step_int8(PG_FUNCTION_ARGS);
+extern Datum varwidth_bucket_int8(PG_FUNCTION_ARGS);
+
#endif /* INT8_H */
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 4286691..11984b8 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1706,3 +1706,94 @@ select length(md5((f1[1]).c2)) from dest;
drop table dest;
drop type textandtext;
+-- Testing for varwidth_bucket(). For convenience, we test both the
+-- generic, float8 and int8 versions of the function in this file.
+-- errors
+SELECT varwidth_bucket('5.0'::text, ARRAY[3, 4]::integer[]);
+ERROR: function varwidth_bucket(text, integer[]) does not exist
+LINE 1: SELECT varwidth_bucket('5.0'::text, ARRAY[3, 4]::integer[]);
+ ^
+HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+SELECT varwidth_bucket(5.0, ARRAY[3, 4, NULL]);
+ERROR: thresholds array must not contain NULLs
+SELECT varwidth_bucket(5.0, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+ERROR: thresholds must be one dimensional array
+SELECT varwidth_bucket(5.0::float8, ARRAY[3, 4, NULL]);
+ERROR: thresholds array must not contain NULLs
+SELECT varwidth_bucket(5.0::float8, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+ERROR: thresholds must be one dimensional array
+SELECT varwidth_bucket(5::bigint, ARRAY[3, 4, NULL]);
+ERROR: thresholds array must not contain NULLs
+SELECT varwidth_bucket(5.0::bigint, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+ERROR: thresholds must be one dimensional array
+-- normal operation
+CREATE TABLE varwidth_bucket_test (operand_num numeric, operand_f8 float8, operand_int8 int8);
+COPY varwidth_bucket_test (operand_num) FROM stdin;
+UPDATE varwidth_bucket_test SET operand_f8 = operand_num::float8, operand_int8 = operand_num::int8;
+SELECT
+ operand_num,
+ varwidth_bucket(operand_num, ARRAY[1, 3, 5, 10]) AS wb_1,
+ varwidth_bucket(operand_f8, ARRAY[1, 3, 5, 10]) AS wb_1f,
+ varwidth_bucket(operand_num, ARRAY[0, 5.5, 9.99]) AS wb_2,
+ varwidth_bucket(operand_f8, ARRAY[0, 5.5, 9.99]) AS wb_2f,
+ varwidth_bucket(operand_num, ARRAY[-6, -5, 2]) AS wb_3,
+ varwidth_bucket(operand_f8, ARRAY[-6, -5, 2]) AS wb_3f
+ FROM varwidth_bucket_test;
+ operand_num | wb_1 | wb_1f | wb_2 | wb_2f | wb_3 | wb_3f
+------------------+------+-------+------+-------+------+-------
+ -5.2 | 0 | 0 | 0 | 0 | 1 | 1
+ -0.0000000001 | 0 | 0 | 0 | 0 | 2 | 2
+ 0.000000000001 | 0 | 0 | 1 | 1 | 2 | 2
+ 1 | 1 | 1 | 1 | 1 | 2 | 2
+ 1.99999999999999 | 1 | 1 | 1 | 1 | 2 | 2
+ 2 | 1 | 1 | 1 | 1 | 3 | 3
+ 2.00000000000001 | 1 | 1 | 1 | 1 | 3 | 3
+ 3 | 2 | 2 | 1 | 1 | 3 | 3
+ 4 | 2 | 2 | 1 | 1 | 3 | 3
+ 4.5 | 2 | 2 | 1 | 1 | 3 | 3
+ 5 | 3 | 3 | 1 | 1 | 3 | 3
+ 5.5 | 3 | 3 | 2 | 2 | 3 | 3
+ 6 | 3 | 3 | 2 | 2 | 3 | 3
+ 7 | 3 | 3 | 2 | 2 | 3 | 3
+ 8 | 3 | 3 | 2 | 2 | 3 | 3
+ 9 | 3 | 3 | 2 | 2 | 3 | 3
+ 9.99999999999999 | 3 | 3 | 3 | 3 | 3 | 3
+ 10 | 4 | 4 | 3 | 3 | 3 | 3
+ 10.0000000000001 | 4 | 4 | 3 | 3 | 3 | 3
+(19 rows)
+
+SELECT
+ operand_int8,
+ varwidth_bucket(operand_int8, ARRAY[1, 3, 5, 10]) AS wb_1,
+ varwidth_bucket(operand_int8, ARRAY[-6, -5, 2]) AS wb_2
+ FROM varwidth_bucket_test;
+ operand_int8 | wb_1 | wb_2
+--------------+------+------
+ -5 | 0 | 2
+ 0 | 0 | 2
+ 0 | 0 | 2
+ 1 | 1 | 2
+ 2 | 1 | 3
+ 2 | 1 | 3
+ 2 | 1 | 3
+ 3 | 2 | 3
+ 4 | 2 | 3
+ 5 | 3 | 3
+ 5 | 3 | 3
+ 6 | 3 | 3
+ 6 | 3 | 3
+ 7 | 3 | 3
+ 8 | 3 | 3
+ 9 | 3 | 3
+ 10 | 4 | 3
+ 10 | 4 | 3
+ 10 | 4 | 3
+(19 rows)
+
+DROP TABLE varwidth_bucket_test;
+SELECT varwidth_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]);
+ varwidth_bucket
+-----------------
+ 2
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index d9f7cbf..701da42 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -476,3 +476,62 @@ drop table src;
select length(md5((f1[1]).c2)) from dest;
drop table dest;
drop type textandtext;
+
+-- Testing for varwidth_bucket(). For convenience, we test both the
+-- generic, float8 and int8 versions of the function in this file.
+
+-- errors
+SELECT varwidth_bucket('5.0'::text, ARRAY[3, 4]::integer[]);
+SELECT varwidth_bucket(5.0, ARRAY[3, 4, NULL]);
+SELECT varwidth_bucket(5.0, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+SELECT varwidth_bucket(5.0::float8, ARRAY[3, 4, NULL]);
+SELECT varwidth_bucket(5.0::float8, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+SELECT varwidth_bucket(5::bigint, ARRAY[3, 4, NULL]);
+SELECT varwidth_bucket(5.0::bigint, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
+
+-- normal operation
+CREATE TABLE varwidth_bucket_test (operand_num numeric, operand_f8 float8, operand_int8 int8);
+
+COPY varwidth_bucket_test (operand_num) FROM stdin;
+-5.2
+-0.0000000001
+0.000000000001
+1
+1.99999999999999
+2
+2.00000000000001
+3
+4
+4.5
+5
+5.5
+6
+7
+8
+9
+9.99999999999999
+10
+10.0000000000001
+\.
+
+UPDATE varwidth_bucket_test SET operand_f8 = operand_num::float8, operand_int8 = operand_num::int8;
+
+SELECT
+ operand_num,
+ varwidth_bucket(operand_num, ARRAY[1, 3, 5, 10]) AS wb_1,
+ varwidth_bucket(operand_f8, ARRAY[1, 3, 5, 10]) AS wb_1f,
+ varwidth_bucket(operand_num, ARRAY[0, 5.5, 9.99]) AS wb_2,
+ varwidth_bucket(operand_f8, ARRAY[0, 5.5, 9.99]) AS wb_2f,
+ varwidth_bucket(operand_num, ARRAY[-6, -5, 2]) AS wb_3,
+ varwidth_bucket(operand_f8, ARRAY[-6, -5, 2]) AS wb_3f
+ FROM varwidth_bucket_test;
+
+SELECT
+ operand_int8,
+ varwidth_bucket(operand_int8, ARRAY[1, 3, 5, 10]) AS wb_1,
+ varwidth_bucket(operand_int8, ARRAY[-6, -5, 2]) AS wb_2
+ FROM varwidth_bucket_test;
+
+DROP TABLE varwidth_bucket_test;
+
+SELECT varwidth_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
