Add support for currently unsupported array functions

[weiznich]

Diesel currently supports the postgres array types. We do not provide built-in support for various operators and methods available for these types. This is a tracking issue for adding support for these operators.

The general strategy for adding support for new methods is as following:

1. Define the operator via define_sql_function!(). These operators can be defined here. See the linked definition of array_append for an example. If there is already an existing definition, this step could be skipped. This function should have a short documentation snippet with en example
2. If the function is generic, add a helper type definition here. Again see the linked definition for an example for the array_append function.
3. Add a test for #[auto_type] support for the newly added function here
4. Check if compile test output is up to date by running TRYBUILD=overwrite cargo test inside of diesel_compile_tests.
5. Submit a PR with the change

Method list:

• array_append: ( anycompatiblearray, anycompatible ) → anycompatiblearray: Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).
• array_cat: ( anycompatiblearray, anycompatiblearray ) → anycompatiblearray: Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator). *
• array_dims: ( anyarray ) → text: Returns a text representation of the array's dimensions. *
• array_fill: ( anyelement, integer[] [, integer[] ] ) → anyarray: Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all 1). **
• array_lenght: ( anyarray, integer ) → integer: Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.) *
• array_lower: ( anyarray, integer ) → integer: Returns the lower bound of the requested array dimension *
• array_ndims: ( anyarray ) → integer: Returns the number of dimensions of the array. *
• array_position: ( anycompatiblearray, anycompatible [, integer ] ) → integer: Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL.**
• array_positions: ( anycompatiblearray, anycompatible ) → integer[]: Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL. NULL is returned only if the array is NULL; if the value is not found in the array, an empty array is returned. *
• array_prepend: ( anycompatible, anycompatiblearray ) → anycompatiblearray: Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator). *
• array_remove: ( anycompatiblearray, anycompatible ) → anycompatiblearray: Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs. *
• array_replace: ( anycompatiblearray, anycompatible, anycompatible ) → anycompatiblearray: Replaces each array element equal to the second argument with the third argument. *
• array_sample: ( array anyarray, n integer ) → anyarray: Returns an array of n items randomly selected from array. n may not exceed the length of array's first dimension. If array is multi-dimensional, an “item” is a slice having a given first subscript. *
• array_shuffle: ( anyarray ) → anyarray: Randomly shuffles the first dimension of the array. *
• array_to_string: ( array anyarray, delimiter text [, null_string text ] ) → text: Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted. **
• array_upper: ( anyarray, integer ) → integer: Returns the upper bound of the requested array dimension. *
• cardinality: ( anyarray ) → integer: Returns the total number of elements in the array, or 0 if the array is empty. *
• trim_array: ( array anyarray, n integer ) → anyarray: Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.
• unnest: ( anyarray ) → setof anyelement: Expands an array into a set of rows. The array's elements are read out in storage order. ***
• unnest: ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]: Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with NULLs. This form is only allowed in a query's FROM clause; see Section 7.2.1.4. ***

For items marked with * the instructions above can be followed as written down

For items marked with ** (any function with at least one optional argument) it might be required to define several variants of the function via the define_sql_type! macro. We want to have at least one variant for each optional argument, if the argument might be commonly skipped. You need to use a different fitting function name in the definition in these cases + use the #[sql_name = "original_name"] attribute to map the function correctly

Items marked with *** cannot be implemented yet as diesel doesn't support sets yet.

Please add a comment to this issue if you plan to work on a specific method.

If there is anything unclear about how to add a support for a specific method just ask and we will try to answer your questions.

[marvin-hansen]

This is amazing, thank you for this roadmap and the ongoing effort to support more array functionality on postgres.

[insky7]

i'm going to give implementing the method array_to_string a try

[danila-b]

I'd like to give array_ndims a try

[wowinter13]

Hello there! I'd like to give array_cat and array_length a try

ETA: next week
CC @weiznich

[valkrypton]

I'd like to work on array_dims function

[edwinlzs]

Hello! I'll add on array_prepend and array_remove

[aznszn]

hi! im doing array_replace

[valkrypton]

i'll be working on array_fill

[aznszn]

working on cardinality

[wowinter13]

working on array_lower

[gushul]

I'll be working on array_upper and array_shuffle

[aznszn]

doin trim_array

[valkrypton]

will be working on array_position and array_positions next

[valkrypton]

ill do array_sample next

[valkrypton]

#4153 (comment)

@weiznich,i tried some queries on my local postgres instance and it seems this method does not exist in postgres
what should i do?

[kw217]

#4153 (comment)

@weiznich,i tried some queries on my local postgres instance and it seems this method does not exist in postgres what should i do?

@valkrypton array_sample and array_shuffle were added in PostgreSQL release 16, so you may need to upgrade to get them.

https://www.postgresql.org/docs/current/release-16.html#RELEASE-16-FUNCTIONS

[weiznich]

@valkrypton As already pointed out: array_sample is a relatively new function, so you cannot use it unless you installed postgres 16. I would go with the same solution as in #4220, which means there should be a doc-test, but that one should be marked as no_run.