Andy Fan <zhihuifan1...@163.com> writes:
>>> My suggestion would be to mirror the signatures of the core random()
>>> functions more closely, and have this:
>>>
>>> 1). rand_array(numvals int, minlen int, maxlen int)
>>> returns setof float8[]
>>>
> ..>
>>> 4). rand_array(numvals int, minlen int, maxlen int,
>>> minval numeric, maxval numeric)
>>> returns setof numeric[]
>
>> this is indeed a more clean and correct APIs, I will use the above ones
>> in the next version. Thanks for the suggestion.
>
> I followed your suggestion in the new attached version. They are not
> only some cleaner APIs for user and but also some cleaner implementation
> in core, Thank for this suggestion as well.
A new version is attached, nothing changed except replace
PG_GETARG_INT16 with PG_GETARG_INT32. PG_GETARG_INT16 is a copy-paste
error.
--
Best Regards
Andy Fan
>From 12fe2c4ac4c982b861134215c2585df63f47f27b Mon Sep 17 00:00:00 2001
From: Andy Fan <zhihuifan1...@163.com>
Date: Mon, 26 Aug 2024 18:50:57 +0800
Subject: [PATCH v20240827 1/1] Add functions rand_array function to
contrib/tablefunc.
It produces an array of numeric_type with its length in the range of
[minlen,maxlen] and each value is in the range of [minval,maxval].
---
contrib/tablefunc/Makefile | 2 +-
contrib/tablefunc/expected/tablefunc.out | 105 ++++++++++++++
contrib/tablefunc/sql/tablefunc.sql | 12 ++
contrib/tablefunc/tablefunc--1.0--1.1.sql | 22 +++
contrib/tablefunc/tablefunc.c | 161 ++++++++++++++++++++++
contrib/tablefunc/tablefunc.control | 2 +-
doc/src/sgml/tablefunc.sgml | 11 ++
src/backend/utils/adt/arrayfuncs.c | 7 +
8 files changed, 320 insertions(+), 2 deletions(-)
create mode 100644 contrib/tablefunc/tablefunc--1.0--1.1.sql
diff --git a/contrib/tablefunc/Makefile b/contrib/tablefunc/Makefile
index 191a3a1d38..f0c67308fd 100644
--- a/contrib/tablefunc/Makefile
+++ b/contrib/tablefunc/Makefile
@@ -3,7 +3,7 @@
MODULES = tablefunc
EXTENSION = tablefunc
-DATA = tablefunc--1.0.sql
+DATA = tablefunc--1.0.sql tablefunc--1.0--1.1.sql
PGFILEDESC = "tablefunc - various functions that return tables"
REGRESS = tablefunc
diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out
index ddece79029..586988881e 100644
--- a/contrib/tablefunc/expected/tablefunc.out
+++ b/contrib/tablefunc/expected/tablefunc.out
@@ -12,6 +12,111 @@ SELECT avg(normal_rand)::int, count(*) FROM normal_rand(100, 250, 0.2);
-- negative number of tuples
SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, 250, 0.2);
ERROR: number of rows cannot be negative
+select setseed(0.8);
+ setseed
+---------
+
+(1 row)
+
+select rand_array(10, 0, 3, 50::int, 80::int);
+ rand_array
+------------
+ {57,58,61}
+ {}
+ {50,50,78}
+ {}
+ {52,52}
+ {55,79,61}
+ {72,78}
+ {}
+ {64,70}
+ {59,78}
+(10 rows)
+
+select setseed(0.8);
+ setseed
+---------
+
+(1 row)
+
+select rand_array(10, 0, 3, 50::bigint, 80::bigint);
+ rand_array
+------------
+ {57,58,61}
+ {}
+ {50,50,78}
+ {}
+ {52,52}
+ {55,79,61}
+ {72,78}
+ {}
+ {64,70}
+ {59,78}
+(10 rows)
+
+select setseed(0.8);
+ setseed
+---------
+
+(1 row)
+
+select rand_array(10, 0, 3, 50::float8, 80::float8);
+ rand_array
+----------------------------------------------------------------
+ {0.2328377064354923,0.2557039868740123,0.3469304653544969}
+ {}
+ {0.000835197565928425,0.011969003780824217,0.8981013605381762}
+ {}
+ {0.08527426384290426,0.06616981677920686}
+ {0.1809935627858228,0.9105400064668479,0.34989620485343464}
+ {0.7139255282088908,0.8803672957879083}
+ {}
+ {0.45650965657162845,0.6253950409407578}
+ {0.30472421749069234,0.8833976124588141}
+(10 rows)
+
+select setseed(0.8);
+ setseed
+---------
+
+(1 row)
+
+select rand_array(10, 0, 3, 50::float4, 80::float4);
+ rand_array
+----------------------------------------------------------------
+ {0.2328377064354923,0.2557039868740123,0.3469304653544969}
+ {}
+ {0.000835197565928425,0.011969003780824217,0.8981013605381762}
+ {}
+ {0.08527426384290426,0.06616981677920686}
+ {0.1809935627858228,0.9105400064668479,0.34989620485343464}
+ {0.7139255282088908,0.8803672957879083}
+ {}
+ {0.45650965657162845,0.6253950409407578}
+ {0.30472421749069234,0.8833976124588141}
+(10 rows)
+
+select setseed(0.8);
+ setseed
+---------
+
+(1 row)
+
+select rand_array(10, 0, 3, 50::numeric, 80::numeric);
+ rand_array
+------------
+ {57,58,61}
+ {}
+ {50,50,78}
+ {}
+ {52,52}
+ {55,79,61}
+ {72,78}
+ {}
+ {64,70}
+ {59,78}
+(10 rows)
+
--
-- crosstab()
--
diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql
index 0fb8e40de2..39e51e8024 100644
--- a/contrib/tablefunc/sql/tablefunc.sql
+++ b/contrib/tablefunc/sql/tablefunc.sql
@@ -8,6 +8,18 @@ SELECT avg(normal_rand)::int, count(*) FROM normal_rand(100, 250, 0.2);
-- negative number of tuples
SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, 250, 0.2);
+select setseed(0.8);
+select rand_array(10, 0, 3, 50::int, 80::int);
+select setseed(0.8);
+select rand_array(10, 0, 3, 50::bigint, 80::bigint);
+select setseed(0.8);
+select rand_array(10, 0, 3, 50::float8, 80::float8);
+select setseed(0.8);
+select rand_array(10, 0, 3, 50::float4, 80::float4);
+select setseed(0.8);
+select rand_array(10, 0, 3, 50::numeric, 80::numeric);
+
+
--
-- crosstab()
--
diff --git a/contrib/tablefunc/tablefunc--1.0--1.1.sql b/contrib/tablefunc/tablefunc--1.0--1.1.sql
new file mode 100644
index 0000000000..973f6aa68c
--- /dev/null
+++ b/contrib/tablefunc/tablefunc--1.0--1.1.sql
@@ -0,0 +1,22 @@
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION tablefunc UPDATE TO '1.1'" to load this file. \quit
+
+CREATE FUNCTION rand_array(numvals int, minlen int, maxlen int, minval int, maxval int)
+RETURNS setof int[]
+AS 'MODULE_PATHNAME','rand_array_int'
+LANGUAGE C VOLATILE STRICT;
+
+CREATE FUNCTION rand_array(numvals int, minlen int, maxlen int, minval bigint, maxval bigint)
+RETURNS setof bigint[]
+AS 'MODULE_PATHNAME','rand_array_bigint'
+LANGUAGE C VOLATILE STRICT;
+
+CREATE FUNCTION rand_array(numvals int, minlen int, maxlen int, minval float8, maxval float8)
+RETURNS setof float8[]
+AS 'MODULE_PATHNAME','rand_array_float8'
+LANGUAGE C VOLATILE STRICT;
+
+CREATE FUNCTION rand_array(numvals int, minlen int, maxlen int, minval numeric, maxval numeric)
+RETURNS setof numeric[]
+AS 'MODULE_PATHNAME','rand_array_numeric'
+LANGUAGE C VOLATILE STRICT;
diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c
index 7d1b5f5143..ffab7e5d8d 100644
--- a/contrib/tablefunc/tablefunc.c
+++ b/contrib/tablefunc/tablefunc.c
@@ -42,7 +42,9 @@
#include "lib/stringinfo.h"
#include "miscadmin.h"
#include "tablefunc.h"
+#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
PG_MODULE_MAGIC;
@@ -91,6 +93,12 @@ typedef struct
bool use_carry; /* use second generated value */
} normal_rand_fctx;
+typedef struct
+{
+ FunctionCallInfo random_val_fcinfo;
+ FunctionCallInfo random_len_fcinfo;
+} rand_array_fctx;
+
#define xpfree(var_) \
do { \
if (var_ != NULL) \
@@ -269,6 +277,159 @@ normal_rand(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
+static Datum
+rand_array_internal(FunctionCallInfo fcinfo, Oid datatype)
+{
+ FuncCallContext *funcctx;
+ Oid random_fn_oid;
+ int num_tuples = PG_GETARG_INT32(0);
+ int minlen = PG_GETARG_INT32(1);
+ int maxlen = PG_GETARG_INT32(2);
+ Datum minval = PG_GETARG_DATUM(3),
+ maxval = PG_GETARG_DATUM(4);
+ rand_array_fctx *fctx;
+
+ if (datatype == INT4OID)
+ random_fn_oid = F_RANDOM_INT4_INT4;
+ else if (datatype == INT8OID)
+ random_fn_oid = F_RANDOM_INT8_INT8;
+ else if (datatype == FLOAT8OID)
+ random_fn_oid = F_RANDOM_;
+ else if (datatype == NUMERICOID)
+ random_fn_oid = F_RANDOM_NUMERIC_NUMERIC;
+ else
+ elog(ERROR, "unsupported type %d for rand_array function.",
+ datatype);
+
+ if (num_tuples < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("number of rows cannot be negative")));
+
+ if (minlen >= maxlen)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("minlen must be greater than maxlen.")));
+
+ if (minlen < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("minlen and maxlen must be greater than zero.")));
+
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+
+ FmgrInfo *random_len_flinfo, *random_val_flinfo;
+ FunctionCallInfo random_len_fcinfo, random_val_fcinfo;
+
+ /* 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);
+
+ funcctx->max_calls = num_tuples;
+
+ /* allocate memory for user context */
+ fctx = (rand_array_fctx *) palloc(sizeof(rand_array_fctx));
+
+ /* build the random_len_fcinfo */
+ random_len_fcinfo = (FunctionCallInfo) palloc0(SizeForFunctionCallInfo(2));
+ random_len_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo));
+ fmgr_info(F_RANDOM_INT4_INT4, random_len_flinfo);
+ InitFunctionCallInfoData(*random_len_fcinfo, random_len_flinfo, 2,
+ InvalidOid, NULL, NULL);
+
+ random_len_fcinfo->args[0].isnull = false;
+ random_len_fcinfo->args[1].isnull = false;
+ random_len_fcinfo->args[0].value = minlen;
+ random_len_fcinfo->args[1].value = maxlen;
+
+ /* build the random_val_fcinfo */
+ random_val_fcinfo = (FunctionCallInfo) palloc0(SizeForFunctionCallInfo(2));
+ random_val_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo));
+ fmgr_info(random_fn_oid, random_val_flinfo);
+ InitFunctionCallInfoData(*random_val_fcinfo, random_val_flinfo, 2,
+ InvalidOid, NULL, NULL);
+
+ random_val_fcinfo->args[0].isnull = false;
+ random_val_fcinfo->args[1].isnull = false;
+ random_val_fcinfo->args[0].value = minval;
+ random_val_fcinfo->args[1].value = maxval;
+
+ fctx->random_val_fcinfo = random_val_fcinfo;
+ fctx->random_len_fcinfo = random_len_fcinfo;
+
+ funcctx->user_fctx = fctx;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ fctx = funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ int array_len;
+ int i;
+ Datum *results;
+
+ array_len = Int32GetDatum(FunctionCallInvoke(fctx->random_len_fcinfo));
+
+ results = palloc(array_len * sizeof(Datum));
+
+ for(i = 0; i < array_len; i++)
+ results[i] = FunctionCallInvoke(fctx->random_val_fcinfo);
+
+
+ SRF_RETURN_NEXT(funcctx, PointerGetDatum(
+ construct_array_builtin(results, array_len, datatype)));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+}
+
+
+PG_FUNCTION_INFO_V1(rand_array_int);
+Datum
+rand_array_int(PG_FUNCTION_ARGS)
+{
+ return rand_array_internal(fcinfo, INT4OID);
+}
+
+
+PG_FUNCTION_INFO_V1(rand_array_bigint);
+Datum
+rand_array_bigint(PG_FUNCTION_ARGS)
+{
+ return rand_array_internal(fcinfo, INT8OID);
+}
+
+
+PG_FUNCTION_INFO_V1(rand_array_float8);
+Datum
+rand_array_float8(PG_FUNCTION_ARGS)
+{
+ return rand_array_internal(fcinfo, FLOAT8OID);
+}
+
+
+PG_FUNCTION_INFO_V1(rand_array_numeric);
+Datum
+rand_array_numeric(PG_FUNCTION_ARGS)
+{
+ return rand_array_internal(fcinfo, NUMERICOID);
+}
+
+
/*
* get_normal_pair()
* Assigns normally distributed (Gaussian) values to a pair of provided
diff --git a/contrib/tablefunc/tablefunc.control b/contrib/tablefunc/tablefunc.control
index 7b25d16170..9cc6222a4f 100644
--- a/contrib/tablefunc/tablefunc.control
+++ b/contrib/tablefunc/tablefunc.control
@@ -1,6 +1,6 @@
# tablefunc extension
comment = 'functions that manipulate whole tables, including crosstab'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/tablefunc'
relocatable = true
trusted = true
diff --git a/doc/src/sgml/tablefunc.sgml b/doc/src/sgml/tablefunc.sgml
index e10fe7009d..ae71374e91 100644
--- a/doc/src/sgml/tablefunc.sgml
+++ b/doc/src/sgml/tablefunc.sgml
@@ -53,6 +53,17 @@
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>rand_array</function> (<parameter>numvals</parameter> <type>integer</type>,<parameter>minlen</parameter> <type>int4</type>, <parameter>maxlen</parameter> <type>int4</type>,<parameter>minval</parameter> <type><replaceable>numeric_type</replaceable></type>, <parameter>maxval</parameter> <type><replaceable>numeric_type</replaceable></type> )
+ <returnvalue>setof <replaceable>numeric_type</replaceable>[]</returnvalue>
+ </para>
+ <para>
+ Produces a set of random numeric_type[], uses the same deterministic pseudo-random number generator as random().
+ </para>
+ </entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<function>crosstab</function> ( <parameter>sql</parameter> <type>text</type> )
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index e5c7e57a5d..0165d97d45 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3404,6 +3404,12 @@ construct_array_builtin(Datum *elems, int nelems, Oid elmtype)
elmalign = TYPALIGN_INT;
break;
+ case FLOAT8OID:
+ elmlen = sizeof(float8);
+ elmbyval = FLOAT8PASSBYVAL;
+ elmalign = TYPALIGN_DOUBLE;
+ break;
+
case INT2OID:
elmlen = sizeof(int16);
elmbyval = true;
@@ -3436,6 +3442,7 @@ construct_array_builtin(Datum *elems, int nelems, Oid elmtype)
break;
case TEXTOID:
+ case NUMERICOID:
elmlen = -1;
elmbyval = false;
elmalign = TYPALIGN_INT;
--
2.45.1
