1. New array functions

array_position(array, elem)

Returns the index (1-based) of the first elem in the 1D array if it exists, otherwise NULL.

SELECT array_position(ARRAY[1.0, 2.0], 1.0) p1, array_position(ARRAY[1.0, 2.0], 3.0) p2;

insertion_point(array, x, [ahead_of_equal])

Looks for the insertion position of x in the 1D array, which must be sorted. Returns its index (1-based).
When the array contains elements equal to x:

• If ahead_of_equal is true, the function returns the position of the first equal element.
• Otherwise, it returns the position of the last equal element plus 1.
  ahead_of_equaldefaults to false.

select insertion_point(Array[1.0, 2.0, 2.0, 3.0], 2.0);
 insertion_point
-----------------
               4

select insertion_point(Array[1.0, 2.0, 2.0, 3.0], 2.0, true);
 insertion_point
-----------------
               2

select insertion_point(Array[1.0, 2.0, 2.0, 3.0], 1.5);
 insertion_point
-----------------
               2

array_sum(array)

Returns the sum of elements in the array.

select array_sum(Array[1.0, 2.0]) sum1, array_sum(Array[[1.0, 3.0]]) sum2;
 sum1 | sum2
------+-----
  3.0 | 4.0

array_count(array)

Returns the number of not NaN elements in the source array.

select array_count(Array[1.0, 2.0]) count1, array_count(Array[[1.0, null]]) count2;
 count1 | count2
--------+--------
      2 |     1

array_avg(array)

Returns the average of elements in the array.

select array_avg(Array[1.0, 2.0]) avg1, array_avg(Array[[1.0, 3.0]]) avg2;
 avg1 | avg2
------+------
  1.5 |  2.0

array_cum_sum(array)

Returns the array of cumulative sums of elements in the array. If array is multidimensional, flattens it first into a single dimension.

select array_cum_sum(Array[1.0, 2.0]) cs1, array_cum_sum(Array[[1.0, 3.0]]) cs2;
     cs1  |    cs2
----------+----------
{1.0,3.0} | {1.0,4.0}

dot_product(array1, array2)

Returns the dot product of the two arrays, which must have the same shape.

select 
    array_dot_product(Array[1.0, 2.0], Array[2.0, 1.0]) p1, 
    array_dot_product(Array[[1.0, 3.0]], Array[[3.0, 1.0]]) p2;
 p1 | p2
----+-----
4.0 | 6.0

shift(array1, x, [fill_value])

Shift array elements along the innermost dimension of the array. When x > 0, it indicates a rightward shift by x positions; otherwise, it signifies a leftward shift by x positions.All newly vacated positions are filled with fill_value, which defaults to Null.

select shift([Array[[1.0, 2.0], [3.0, 4.0]], 1);
  column
-----------
 {{Null,1.0},{Null, 3.0}}

(+ - * /) between array and scalar value

+

select Array[1.0, 2.0] + 1, 1 + Array[1.0, 2.0];
  column   |  column1
-----------+-----------
 {2.0,3.0} | {2.0,3.0}

-

select Array[1.0, 2.0] - 1;
  column
-----------
 {0.0,1.0}

*

select Array[1.0, 2.0] * 2, 2 * Array[1.0, 2.0];
  column   |  column1
-----------+-----------
 {2.0,4.0} | {2.0,4.0}

/

select Array[1.0, 2.0] / 2;
  column
-----------
 {0.5,1.0}

2. Auto-broadcasting for binary array operators

Broadcast rules on (+ - * /) between arrays

https://numpy.org/doc/stable/user/basics.broadcasting.html#general-broadcasting-rules

Two array shapes are compatiable only when all of their dimensions are compatible.

Starting with the rightmost dimension moving to the left, two dimensions are compatible when:

• they are equal, or
• one of them is 1

The two arrays do not need to have the same number of dimensions. The resulting array will have the same number of dimensions as the input array with the greatest number of dimensions, where the size of each dimension is the largest size of the corresponding dimension among the input arrays. Note that missing dimensions are assumed to have size one.

select Array[[0.0, 0, 0], [10, 10, 10], [20, 20, 20], [30, 30, 30]] * Array[0, 1, 2];
                             column
-----------------------------------------------------------------
 {{0.0,0.0,0.0},{0.0,10.0,20.0},{0.0,20.0,40.0},{0.0,30.0,60.0}}

Broadcast rules on matmul function

• If the left array is 1-D, it is promoted to a matrix by prepending a 1 to its dimensions.
• If the right array is 1-D, it is promoted to a matrix by appending a 1 to its dimensions.

select matmul(Array[[2.0, 3.0], [4.0, 5.0], [6.0, 7.0]], Array[1.0, 2]);
        matmul
-----------------------
 {{8.0},{14.0},{20.0}}

3. Performance

1. The PR expands the meaning of isVanilla to cover all cases where all the array elements occur in a contiguous block, arranged in the row-major order. This may be an arbitrary contiguous block within the one covered by the underlying flat array view.
2. arraySum, arrayAvg and arrayCount use vectorized operations when the array is vanilla.