Hi Andy
On 08.06.24 08:05, Andy Fan wrote:
> Here is a new function which could produce an array of numbers with a
> controllable array length and duplicated elements in these arrays. I
> used it when working with gin index, and I think it would be helpful for
> others as well, so here it is.
>
> select * from normal_rand_array(5, 10, 1.8::numeric, 3.5::numeric);
> normal_rand_array
> -----------------------------------------------
> {3.3,2.3,2.7,3.2,2.0,2.7,3.4,2.7,2.3,2.9}
> {3.3,1.8,2.9,3.4,2.0,1.8,2.0,3.5,2.8,2.5}
> {2.1,1.9,2.3,1.9,2.5,2.7,2.4,2.9,1.8}
> {2.3,2.5,2.4,2.7,2.7,2.3,2.9,3.3,3.3,1.9,3.5}
> {2.8,3.4,2.7,1.8,3.3,2.3,2.2,3.5,2.6,2.5}
> (5 rows)
>
> select * from normal_rand_array(5, 10, 1.8::int4, 3.5::int4);
> normal_rand_array
> -------------------------------------
> {3,2,2,3,4,2}
> {2,4,2,3,3,3,3,2,2,3,3,2,3,2}
> {2,4,3}
> {4,2,3,4,2,4,2,2,3,4,3,3,2,4,4,2,3}
> {4,3,3,4,3,3,4,2,4}
> (5 rows)
>
> the 5 means it needs to produce 5 rows in total and the 10 is the
> average array length, and 1.8 is the minvalue for the random function
> and 3.5 is the maxvalue.
>
When either minval or maxval exceeds int4 the function cannot be
executed/found
SELECT * FROM normal_rand_array(5, 10, 8, 42::bigint);
ERROR: function normal_rand_array(integer, integer, integer, bigint)
does not exist
LINE 1: SELECT * FROM normal_rand_array(5, 10, 8, 42::bigint);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
---
SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42);
ERROR: function normal_rand_array(integer, integer, bigint, integer)
does not exist
LINE 1: SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
---
However, when both are int8 it works fine:
SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42::bigint);
normal_rand_array
--------------------------------------------------
{29,38,31,10,23,39,9,32}
{8,39,19,31,29,15,17,15,36,20,33,19}
{15,18,42,19}
{16,31,33,11,14,20,24,9,12,17,22,42,41,24,11,41}
{15,11,36,8,28,37}
(5 rows)
---
Is it the expected behaviour?
In some cases the function returns an empty array. Is it also expected?
SELECT count(*)
FROM normal_rand_array(100000, 10, 8, 42) i
WHERE array_length(i,1) IS NULL;
count
-------
4533
(1 row)
In both cases, perhaps mentioning these behaviors in the docs would
avoid some confusion.
Thanks!
Best,
--
Jim
