Re: New function normal_rand_array function to contrib/tablefunc.

On Wed, Nov 6, 2024 at 12:20 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> On Tue, 5 Nov 2024 at 15:23, Aleksander Alekseev
> <aleksander(at)timescale(dot)com> wrote:
> >
> > > > =# SELECT array_random(1, 10, random(0, 3)) FROM generate_series( ... )
> > > > {5}
> > > > {1, 3, 8}
> > > > {7, 6}
> > > > ...
> > >
> > > Yeah, that looks like a neater API.
> > >
> > > Something that bothers me somewhat is that it's completely trivial for
> > > the user to write such a function for themselves, so is it really
> > > useful enough to include in core?
> >
> > I think it would be useful. Many users don't bother writing C
> > extensions for tasks like this. So at least our implementation is
> > going to be faster.
> >
>

it's fine to add such a function, IMHO.
we already have array_fill, array_fill fill the same value repeatedly,
here we can fill an array with random value. So this is better than array_fill.

The most trivial query I can think of is
select array_agg((array[random(1, 10), random(1,10), random(1, 10)]));
but this can only be up to two dimensional.
Users wishing to write such functions with several dimensions can be tricky.

> If we are going to add such a function to core, then I think we should
> make it consistent and at least as flexible as the other array
> functions, and support multi-dimensional arrays with optional
> non-default lower-bounds, like array_fill(). I.e., something like:
>
> random_array(min int, max int, dims int[] [, lbounds int[]]) -> int[]
>
> Returns an array filled with random values in the range min <= x <= max,
> having dimensions of the lengths specified by dims. The optional lbounds
> argument supplies lower-bound values for each dimension (which default
> to all 1).
>
please try the attached.

instead of
> random_array(min int, max int, dims int[] [, lbounds int[]]) -> int[]
i choose
array_random(min int, max int, dims int[] [, lbounds int[]]) -> int[]

I placed it in src/backend/utils/adt/arrayfuncs.c, naming it as array_random
to align with the surrounding function name convention.
it can be changed.

Another issue is
we can not use function signature as array_random(anyelement,
anyelement, int[] [, int[]])
because currently, we cannot resolve type conflict for cases like
array_random(1, 2::bigint).
In this case, the first argument should be promoted to bigint.

For example:
create or replace function polyf(x anyelement, y anyelement) returns
anyelement as $$
select x + 1
$$ language sql;

select polyf(1, 2::bigint);
ERROR: function polyf(integer, bigint) does not exist
select polyf(1::bigint, 2);
ERROR: function polyf(bigint, integer) does not exist

so i invented 3 functions, similar to what we did with random().
now it looks like:

\df array_random
List of functions
Schema | Name | Result data type |
Argument data types | Type
------------+--------------+------------------+-------------------------------------------------------------------------------------+------
pg_catalog | array_random | bigint[] | min bigint, max
bigint, dims integer[], lbounds integer[] DEFAULT NULL::integer[] |
func
pg_catalog | array_random | integer[] | min integer, max
integer, dims integer[], lbounds integer[] DEFAULT NULL::integer[] |
func
pg_catalog | array_random | numeric[] | min numeric, max
numeric, dims integer[], lbounds integer[] DEFAULT NULL::integer[] |
func

doc is not there yet.