diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 70a1bd9..7347de1 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT NULLIF(value, '(none)') ...
*** 10214,10222 ****
--- 10214,10237 ----
array_dims
+ array_except
+
+
+ array_except_all
+
+
array_fill
+ array_intersect
+
+
+ array_intersect_all
+
+
+ array_is_unique
+
+
array_length
*************** SELECT NULLIF(value, '(none)') ...
*** 10226,10240 ****
array_prepend
array_to_string
!
array_upper
string_to_array
unnest
--- 10241,10273 ----
array_prepend
+ array_sort
+
+
array_to_string
!
! array_union
!
!
! array_union_all
!
!
! array_unique
!
!
array_upper
+ cardinality
+
+
string_to_array
+ trim_array
+
+
unnest
*************** SELECT NULLIF(value, '(none)') ...
*** 10298,10303 ****
--- 10331,10358 ----
+ array_except(anyarray, anyarray)
+
+
+ anyarray
+ subtraction of two arrays and remove duplicated values
+ array_except(ARRAY[1,1,2], ARRAY[1,3])
+ {2}
+
+
+
+
+ array_except_all(anyarray, anyarray)
+
+
+ anyarray
+ subtraction of two arrays
+ array_except_all(ARRAY[1,1,2], ARRAY[1,3])
+ {1,2}
+
+
+
+
array_fill(anyelement, int[],
, int[])
*************** SELECT NULLIF(value, '(none)') ...
*** 10311,10316 ****
--- 10366,10404 ----
+ array_intersect(anyarray, anyarray)
+
+
+ anyarray
+ intersection of two arrays and remove duplicated values
+ array_intersect(ARRAY[1,1,2], ARRAY[1,1,3])
+ {1}
+
+
+
+
+ array_intersect_all(anyarray, anyarray)
+
+
+ anyarray
+ intersection of two arrays
+ array_intersect_all(ARRAY[1,1,2], ARRAY[1,1,3])
+ {1,1}
+
+
+
+
+ array_is_unique(anyarray)
+
+
+ bool
+ returns true if no duplicated elements in an array
+ array_is_unique(ARRAY[1,NULL]), array_is_unique(ARRAY[1,NULL,NULL])
+ t, f
+
+
+
+
array_length(anyarray, int)
*************** SELECT NULLIF(value, '(none)') ...
*** 10344,10349 ****
--- 10432,10448 ----
+ array_sort(anyarray)
+
+
+ anyarray
+ sort elements in an array in ascending order
+ array_sort(ARRAY[3,2,NULL,1])
+ {1,2,3,NULL}
+
+
+
+
array_to_string(anyarray, text , text)
*************** SELECT NULLIF(value, '(none)') ...
*** 10356,10361 ****
--- 10455,10493 ----
+ array_union(anyarray, anyarray)
+
+
+ text
+ union of two arrays and remove duplicated values
+ array_union(ARRAY[2,3], ARRAY[1,1,2])
+ {1,2,3}
+
+
+
+
+ array_union_all(anyarray, anyarray)
+
+
+ text
+ union of two arrays, same as array_cat
+ array_union_all(ARRAY[2,3], ARRAY[1,1,2])
+ {2,3,1,1,2}
+
+
+
+
+ array_unique(anyarray)
+
+
+ anyarray
+ remove duplicated elements in an array
+ array_unique(ARRAY[1,3,2,3,NULL,1,NULL])
+ {1,2,3,NULL}
+
+
+
+
array_upper(anyarray, int)
*************** SELECT NULLIF(value, '(none)') ...
*** 10367,10372 ****
--- 10499,10515 ----
+ array_trim(anyarray, integer)
+
+
+ anyarray
+ remove supplied number of elements at end of an array
+ array_trim(ARRAY[1,2,3], 2), array_trim(ARRAY[[1,2],[3,4]], 1)
+ {1}, {{1,2}}
+
+
+
+
string_to_array(text, text , text)
*************** SELECT NULLIF(value, '(none)') ...
*** 10379,10384 ****
--- 10522,10549 ----
+ cardinality(anyarray)
+
+
+ int
+ returns the number of elements in an array
+ cardinality(ARRAY[1,2,3])
+ 3
+
+
+
+
+ trim_array(anyarray, integer)
+
+
+ anyarray
+ same as array_trim
+ trim_array(ARRAY[1,2,3], 2), trim_array(ARRAY[[1,2],[3,4]], 1)
+ {1}, {{1,2}}
+
+
+
+
unnest(anyarray)
*************** SELECT NULLIF(value, '(none)') ...
*** 10420,10428 ****
See also about the aggregate
! function array_agg for use with arrays.
--- 10585,10605 ----
+
+
+ Multidimensional arrays are not supported by
+ array_except, array_except_all,
+ array_intersect, array_intersect_all,
+ array_is_unique, array_sort,
+ array_union, array_union_all,
+ and array_unique in the current version.
+
+
+
See also about the aggregate
! function array_agg, array_fusion,
! and array_intersection for use with arrays.
*************** SELECT NULLIF(value, '(none)') ...
*** 10468,10474 ****
array_agg(expression)
! any
array of the argument type
--- 10645,10651 ----
array_agg(expression)
! any non-array
array of the argument type
*************** SELECT NULLIF(value, '(none)') ...
*** 10479,10484 ****
--- 10656,10693 ----
+ array_fusion
+
+ array_fusion(expression)
+
+
+ any array
+
+
+ same as argument type
+
+ concatenation of input arrays
+
+
+
+
+
+ array_intersection
+
+ array_intersection(expression)
+
+
+ any array
+
+
+ same as argument type
+
+ intersection of input arrays
+
+
+
+
+
average
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 499d357..9b3276f 100644
*** a/src/backend/utils/adt/array_userfuncs.c
--- b/src/backend/utils/adt/array_userfuncs.c
***************
*** 15,21 ****
--- 15,24 ----
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+ #include "utils/typcache.h"
+ static void check_concatenable(Oid element_type1, Oid element_type2);
+ static void check_comparable(Oid element_type1, Oid element_type2);
/*-----------------------------------------------------------------------------
* array_push :
*************** array_cat(PG_FUNCTION_ARGS)
*** 218,231 ****
element_type2 = ARR_ELEMTYPE(v2);
/* Check we have matching element types */
! if (element_type1 != element_type2)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("cannot concatenate incompatible arrays"),
! errdetail("Arrays with element types %s and %s are not "
! "compatible for concatenation.",
! format_type_be(element_type1),
! format_type_be(element_type2))));
/* OK, use it */
element_type = element_type1;
--- 221,227 ----
element_type2 = ARR_ELEMTYPE(v2);
/* Check we have matching element types */
! check_concatenable(element_type1, element_type2);
/* OK, use it */
element_type = element_type1;
*************** array_agg_finalfn(PG_FUNCTION_ARGS)
*** 544,546 ****
--- 540,1328 ----
PG_RETURN_DATUM(result);
}
+
+ /*
+ * array_cardinality :
+ * Return the number of elements in an array. If the array is
+ * multi-demensional, the total number of elements is returned.
+ */
+ Datum
+ array_cardinality(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
+ int nitems;
+
+ nitems = ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v));
+
+ PG_RETURN_INT32(nitems);
+ }
+
+ /*
+ * trim_array :
+ * Remove elements or slaces at end of an array. It is same as
+ * array[array_lower(array, 1):array_upper(array, 1) - ntrimmed].
+ */
+ Datum
+ trim_array(PG_FUNCTION_ARGS)
+ {
+ ArrayType *result;
+ ArrayType *v;
+ int32 ntrimmed = PG_GETARG_INT32(1);
+ int ndims;
+ int nitems;
+ int lower[MAXDIM];
+ int upper[MAXDIM];
+ int i;
+ int arrtyplen;
+ Oid elmtype;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+
+ if (ntrimmed < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
+ errmsg("number of trimmed elements (%d) must not be negative", ntrimmed)));
+
+ v = PG_GETARG_ARRAYTYPE_P(0);
+ ndims = ARR_NDIM(v);
+
+ /* Trimmed elements must not be greater than the first dimension length. */
+ nitems = (ndims == 0 ? 0 : ARR_DIMS(v)[0]);
+ if (ntrimmed > nitems)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
+ errmsg("number of trimmed elements (%d) is greater than number of elements (%d)", ntrimmed, nitems)));
+
+ /* Save lower and upper bounds to restore them at end. */
+ for (i = 0; i < ndims; i++)
+ {
+ lower[i] = ARR_LBOUND(v)[i];
+ upper[i] = lower[i] - 1 + ARR_DIMS(v)[i];
+ }
+
+ /* Remove elements from the first dimension and get a new slice. */
+ if (ndims > 0)
+ upper[0] -= ntrimmed;
+ arrtyplen = get_typlen(get_fn_expr_argtype(fcinfo->flinfo, 0));
+ elmtype = ARR_ELEMTYPE(v);
+ get_typlenbyvalalign(elmtype, &elmlen, &elmbyval, &elmalign);
+ result = array_get_slice(
+ v, ndims, upper, lower, arrtyplen, elmlen, elmbyval, elmalign);
+
+ /* Adjust lower bounds because array_get_slice reset the value to 1. */
+ for (i = 0; i < ndims; i++)
+ ARR_LBOUND(result)[i] = lower[i];
+
+ PG_FREE_IF_COPY(v, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_trim :
+ * An alias for trim_array for naming consistency.
+ */
+ Datum
+ array_trim(PG_FUNCTION_ARGS)
+ {
+ return trim_array(fcinfo);
+ }
+
+ /*
+ * Find TypeCacheEntry with comparison functions for element_type.
+ * We arrange to look up the compare functions only once per series of
+ * calls, assuming the element type doesn't change underneath us.
+ */
+ static TypeCacheEntry *
+ get_type_cache(Oid element_type, void **fn_extra)
+ {
+ TypeCacheEntry *type;
+
+ type = (TypeCacheEntry *) *fn_extra;
+ if (type == NULL ||
+ type->type_id != element_type)
+ {
+ type = lookup_type_cache(element_type,
+ TYPECACHE_EQ_OPR_FINFO | TYPECACHE_CMP_PROC_FINFO);
+ if (!OidIsValid(type->eq_opr_finfo.fn_oid) ||
+ !OidIsValid(type->cmp_proc_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify comparison functions for type %s",
+ format_type_be(element_type))));
+ *fn_extra = type;
+ }
+
+ return type;
+ }
+
+ static int
+ compare_elements(const void *a, const void *b, void *arg)
+ {
+ return DatumGetInt32(FunctionCall2(
+ (FmgrInfo *) arg, *(const Datum *) a, *(const Datum *) b));
+ }
+
+ /*
+ * Sort values and move nulls to the end.
+ * Returns number of non-null elements as an option.
+ */
+ static void
+ sort_elements(TypeCacheEntry *type, Datum *values, bool *nulls,
+ int nitems, int *nonnulls)
+ {
+ int n,
+ i;
+
+ if (nulls == NULL)
+ n = nitems;
+ else
+ {
+ /* move nulls to end of the array */
+ for (i = n = 0; i < nitems; i++)
+ {
+ if (!nulls[i])
+ {
+ values[n] = values[i];
+ nulls[n] = false;
+ n++;
+ }
+ }
+ for (i = n; i < nitems; i++)
+ nulls[i] = true;
+ }
+
+ /* sort non-null values */
+ qsort_arg(values, n, sizeof(Datum),
+ compare_elements, &type->cmp_proc_finfo);
+
+ if (nonnulls)
+ *nonnulls = n;
+ }
+
+ /*
+ * Remove duplicated values in already sorted elements. The values, nulls,
+ * nitems, and nonnulls parameters are modified directly. Note that only
+ * one null value will be kept in the result when there are some nulls.
+ */
+ static void
+ unique_elements(Datum *values, bool *nulls, int *nitems, int *nonnulls,
+ TypeCacheEntry *type)
+ {
+ int i,
+ n,
+ nvalues = *nonnulls;
+ bool has_nulls = (*nonnulls < *nitems);
+
+ for (i = n = 1; i < nvalues; i++)
+ {
+ if (!DatumGetBool(FunctionCall2(
+ &type->eq_opr_finfo, values[i - 1], values[i])))
+ {
+ Assert(!nulls[n]);
+ values[n++] = values[i];
+ }
+ }
+ *nonnulls = n;
+ if (has_nulls)
+ nulls[n++] = true;
+ *nitems = n;
+ }
+
+ /*
+ * Deconstruct an array to a list of elements and sort them. Returns values,
+ * null, number of all elements and non-null elements as output parameters.
+ * nulls and nonnulls can be NULLs.
+ */
+ static void
+ deconstruct_and_sort(ArrayType *array, Datum **values, bool **nulls,
+ int *nitems, int *nonnulls, TypeCacheEntry *type)
+ {
+ Oid element_type = ARR_ELEMTYPE(array);
+ bool *tmp_nulls;
+
+ AssertArg(values != NULL);
+ AssertArg(nitems != NULL);
+
+ /* TODO: Currently we do not support multi-dimensional arrays. */
+ if (ARR_NDIM(array) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use multi-dimensional arrays")));
+
+ deconstruct_array(array,
+ element_type,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ values, &tmp_nulls, nitems);
+ sort_elements(type, *values, tmp_nulls, *nitems, nonnulls);
+
+ if (nulls)
+ *nulls = tmp_nulls;
+ }
+
+ /*
+ * A worker for array_sort, array_unique, and other functions.
+ * Sort an array in ascending order, and optionally remove duplicated values.
+ */
+ static ArrayType *
+ array_sort_internal(ArrayType *array, bool unique, void **fn_extra)
+ {
+ TypeCacheEntry *type;
+ Datum *values;
+ bool *nulls;
+ int nitems,
+ nonnulls;
+ int ndims = ARR_NDIM(array);
+ int lbs;
+ Oid element_type = ARR_ELEMTYPE(array);
+
+ /* TODO: Currently we do not support multi-dimensional arrays. */
+ if (ndims > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use multi-dimensional arrays")));
+
+ /* Save the lower bound. */
+ lbs = (ndims > 0 ? ARR_LBOUND(array)[0] : 1);
+
+ type = get_type_cache(element_type, fn_extra);
+ deconstruct_and_sort(array, &values, &nulls, &nitems, &nonnulls, type);
+ if (unique)
+ unique_elements(values, nulls, &nitems, &nonnulls, type);
+
+ return construct_md_array(values, nulls, 1, &nitems, &lbs, element_type,
+ type->typlen, type->typbyval, type->typalign);
+ }
+
+ /*
+ * array_sort :
+ * Sort an array in ascending order. Nulls are in the last.
+ */
+ Datum
+ array_sort(PG_FUNCTION_ARGS)
+ {
+ ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
+ ArrayType *result;
+
+ result = array_sort_internal(array, false, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(array, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_unique :
+ * Remove duplicated elements in an array, and eventually sorted.
+ */
+ Datum
+ array_unique(PG_FUNCTION_ARGS)
+ {
+ ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
+ ArrayType *result;
+
+ result = array_sort_internal(array, true, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(array, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_is_unique :
+ * Return true iff an array has not duplicated values.
+ * Note that only one null is allowed in an unique array.
+ */
+ Datum
+ array_is_unique(PG_FUNCTION_ARGS)
+ {
+ ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
+ bool result;
+ Datum *values;
+ int nitems,
+ nonnulls;
+ int i;
+ TypeCacheEntry *type;
+
+ type = get_type_cache(ARR_ELEMTYPE(array), &fcinfo->flinfo->fn_extra);
+ deconstruct_and_sort(array, &values, NULL, &nitems, &nonnulls, type);
+ if (nitems > nonnulls + 1)
+ {
+ /* only one null is allowd */
+ result = false;
+ }
+ else
+ {
+ result = true;
+ /* compare for each adjacent */
+ for (i = 1; i < nonnulls; i++)
+ {
+ if (DatumGetBool(FunctionCall2(
+ &type->eq_opr_finfo, values[i - 1], values[i])))
+ {
+ result = false;
+ break;
+ }
+ }
+ }
+
+ PG_FREE_IF_COPY(array, 0);
+ PG_RETURN_BOOL(result);
+ }
+
+ /*
+ * array_union :
+ * Concatinate two arrays, and remove duplicated values.
+ */
+ Datum
+ array_union(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v1,
+ *v2;
+ ArrayType *result;
+ Datum *values,
+ *values1,
+ *values2;
+ bool *nulls,
+ *nulls1,
+ *nulls2;
+ int nitems,
+ nitems1,
+ nitems2,
+ nonnulls;
+ Oid element_type1,
+ element_type2;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ if (PG_ARGISNULL(0) && PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ /* Concatenating a null array is a no-op, just return the other input */
+ if (PG_ARGISNULL(0))
+ {
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+ result = array_sort_internal(v2, true, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(v2, 1);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+ if (PG_ARGISNULL(1))
+ {
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ result = array_sort_internal(v1, true, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(v1, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+
+ /* TODO: Currently we do not support multi-dimensional arrays. */
+ if (ARR_NDIM(v1) > 1 || ARR_NDIM(v2) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use multi-dimensional arrays")));
+
+ element_type1 = ARR_ELEMTYPE(v1);
+ element_type2 = ARR_ELEMTYPE(v2);
+
+ check_concatenable(element_type1, element_type2);
+ type = get_type_cache(element_type1, &fcinfo->flinfo->fn_extra);
+ deconstruct_array(v1,
+ element_type1,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ &values1, &nulls1, &nitems1);
+ deconstruct_array(v2,
+ element_type2,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ &values2, &nulls2, &nitems2);
+
+ nitems = nitems1 + nitems2;
+ values = (Datum *) palloc(sizeof(Datum) * nitems);
+ nulls = (bool *) palloc(sizeof(bool) * nitems);
+
+ memcpy(values, values1, sizeof(Datum) * nitems1);
+ memcpy(values + nitems1, values2, sizeof(Datum) * nitems2);
+ memcpy(nulls, nulls1, sizeof(bool) * nitems1);
+ memcpy(nulls + nitems1, nulls2, sizeof(bool) * nitems2);
+
+ sort_elements(type, values, nulls, nitems, &nonnulls);
+ unique_elements(values, nulls, &nitems, &nonnulls, type);
+ result = construct_md_array(values, nulls, 1, &nitems, &lbs,
+ element_type1,
+ type->typlen,
+ type->typbyval,
+ type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_union_all :
+ * An alias for array_cat for naming consistency.
+ */
+ Datum
+ array_union_all(PG_FUNCTION_ARGS)
+ {
+ return array_cat(fcinfo);
+ }
+
+ /*
+ * Intersection of two sorted arrays. The first array is modified directly.
+ * Return length of the result.
+ */
+ static int
+ intersect_sorted_arrays(TypeCacheEntry *type,
+ Datum *values1, bool *nulls1, int nitems1,
+ const Datum *values2, const bool *nulls2, int nitems2)
+ {
+ int n1,
+ n2,
+ n;
+
+ /* add non-nulls */
+ for (n = n1 = n2 = 0;
+ n1 < nitems1 && !nulls1[n1] &&
+ n2 < nitems2 && !nulls2[n2];)
+ {
+ int r = DatumGetInt32(FunctionCall2(&type->cmp_proc_finfo,
+ values1[n1], values2[n2]));
+ if (r == 0)
+ values1[n++] = values1[n1];
+ if (r <= 0)
+ n1++;
+ if (r >= 0)
+ n2++;
+ }
+
+ /* skip non-nulls */
+ for (; n1 < nitems1 && !nulls1[n1]; n1++) {}
+ for (; n2 < nitems2 && !nulls2[n2]; n2++) {}
+
+ /* add nulls */
+ for (; n1 < nitems1 && n2 < nitems2; n1++, n2++, n++)
+ nulls1[n] = true;
+
+ return n;
+ }
+
+ /*
+ * A worker for array_intersect and array_intersect_all.
+ */
+ static Datum
+ array_intersect_internal(PG_FUNCTION_ARGS, bool all)
+ {
+ ArrayType *v1 = PG_GETARG_ARRAYTYPE_P(0);
+ ArrayType *v2 = PG_GETARG_ARRAYTYPE_P(1);
+
+ ArrayType *result;
+ Oid element_type = ARR_ELEMTYPE(v1);
+ Datum *values1,
+ *values2;
+ bool *nulls1,
+ *nulls2;
+ int nitems1,
+ nitems2,
+ nonnulls1,
+ nonnulls2;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ check_comparable(element_type, ARR_ELEMTYPE(v2));
+ type = get_type_cache(element_type, &fcinfo->flinfo->fn_extra);
+
+ deconstruct_and_sort(v1, &values1, &nulls1, &nitems1, &nonnulls1, type);
+ if (!all)
+ unique_elements(values1, nulls1, &nitems1, &nonnulls1, type);
+ deconstruct_and_sort(v2, &values2, &nulls2, &nitems2, &nonnulls2, type);
+ if (!all)
+ unique_elements(values2, nulls2, &nitems2, &nonnulls2, type);
+
+ nitems1 = intersect_sorted_arrays(type,
+ values1, nulls1, nitems1,
+ values2, nulls2, nitems2);
+ result = construct_md_array(values1, nulls1, 1, &nitems1, &lbs,
+ element_type, type->typlen,
+ type->typbyval, type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_intersect :
+ * Intersection of two arrays and remove duplicated values.
+ */
+ Datum
+ array_intersect(PG_FUNCTION_ARGS)
+ {
+ return array_intersect_internal(fcinfo, false);
+ }
+
+ /*
+ * array_intersect_all :
+ * Intersection of two arrays.
+ */
+ Datum
+ array_intersect_all(PG_FUNCTION_ARGS)
+ {
+ return array_intersect_internal(fcinfo, true);
+ }
+
+ /*
+ * A worker for array_except and array_except_all.
+ */
+ static Datum
+ array_except_internal(PG_FUNCTION_ARGS, bool all)
+ {
+ ArrayType *v1;
+ ArrayType *v2;
+ ArrayType *result;
+ Oid element_type;
+ Datum *values1,
+ *values2;
+ bool *nulls1,
+ *nulls2;
+ int nitems1,
+ nitems2,
+ nonnulls1,
+ nonnulls2;
+ int n1,
+ n2,
+ n;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+
+ /* fast path for except null */
+ if (PG_ARGISNULL(1))
+ {
+ result = array_sort_internal(v1, !all, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(v1, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+ element_type = ARR_ELEMTYPE(v1);
+ check_concatenable(element_type, ARR_ELEMTYPE(v2));
+ type = get_type_cache(element_type, &fcinfo->flinfo->fn_extra);
+
+ deconstruct_and_sort(v1, &values1, &nulls1, &nitems1, &nonnulls1, type);
+ if (!all)
+ unique_elements(values1, nulls1, &nitems1, &nonnulls1, type);
+ deconstruct_and_sort(v2, &values2, &nulls2, &nitems2, &nonnulls2, type);
+ if (!all)
+ unique_elements(values2, nulls2, &nitems2, &nonnulls2, type);
+
+ /* add non-nulls */
+ for (n = n1 = n2 = 0; n1 < nonnulls1 && n2 < nonnulls2;)
+ {
+ int r = DatumGetInt32(FunctionCall2(&type->cmp_proc_finfo,
+ values1[n1], values2[n2]));
+ if (r < 0)
+ values1[n++] = values1[n1++];
+ else
+ n2++;
+ if (r == 0)
+ n1++;
+ }
+ for (; n1 < nonnulls1; n1++, n++)
+ values1[n] = values1[n1];
+
+ /* add nulls */
+ for (n1 = nonnulls1; n1 < nitems1 - (nitems2 - nonnulls2); n1++, n++)
+ nulls1[n] = true;
+
+ result = construct_md_array(values1, nulls1, 1, &n, &lbs, element_type,
+ type->typlen, type->typbyval, type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * array_except :
+ * Subtraction of two arrays and remove duplicated values.
+ */
+ Datum
+ array_except(PG_FUNCTION_ARGS)
+ {
+ return array_except_internal(fcinfo, false);
+ }
+
+ /*
+ * array_except_all :
+ * Subtraction of two arrays.
+ */
+ Datum
+ array_except_all(PG_FUNCTION_ARGS)
+ {
+ return array_except_internal(fcinfo, true);
+ }
+
+ /*
+ * array_fusion :
+ * Similar to array_agg, but the input values are arrays.
+ */
+ Datum
+ array_fusion_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ArrayBuildState *state;
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "array_fusion_transfn called in non-aggregate context");
+ }
+
+ state = PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0);
+ if (!PG_ARGISNULL(1))
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(1);
+ Oid elmtype;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ Datum *elems;
+ bool *nulls;
+ int nitems;
+ int i;
+
+ if (ARR_NDIM(v) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use multi-dimensional arrays")));
+
+ elmtype = ARR_ELEMTYPE(v);
+ get_typlenbyvalalign(elmtype, &elmlen, &elmbyval, &elmalign);
+ deconstruct_array(v, elmtype, elmlen, elmbyval, elmalign,
+ &elems, &nulls, &nitems);
+ for (i = 0; i < nitems; i++)
+ state = accumArrayResult(state, elems[i], nulls[i],
+ elmtype, aggcontext);
+
+ PG_FREE_IF_COPY(v, 1);
+ }
+
+ PG_RETURN_POINTER(state);
+ }
+
+ /*
+ * array_intersection :
+ * Intersection of all input arrays.
+ */
+ typedef struct ArrayIntersectionState
+ {
+ Oid element_type;
+ int nitems;
+ Datum *values;
+ bool *nulls;
+ } ArrayIntersectionState;
+
+ Datum
+ array_intersection_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ArrayIntersectionState *state;
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "array_intersection_transfn called in non-aggregate context");
+ }
+
+ state = PG_ARGISNULL(0) ? NULL
+ : (ArrayIntersectionState *) PG_GETARG_POINTER(0);
+ if (!PG_ARGISNULL(1))
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(1);
+ TypeCacheEntry *type;
+
+ type = get_type_cache(ARR_ELEMTYPE(v), &fcinfo->flinfo->fn_extra);
+
+ if (state == NULL)
+ {
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = (ArrayIntersectionState *)
+ palloc(sizeof(ArrayIntersectionState));
+ state->element_type = ARR_ELEMTYPE(v);
+ deconstruct_and_sort(v, &state->values, &state->nulls,
+ &state->nitems, NULL, type);
+ MemoryContextSwitchTo(oldcontext);
+ }
+ else
+ {
+ Datum *values;
+ bool *nulls;
+ int nitems;
+
+ check_concatenable(state->element_type, ARR_ELEMTYPE(v));
+ deconstruct_and_sort(v, &values, &nulls, &nitems, NULL, type);
+ state->nitems = intersect_sorted_arrays(type,
+ state->values, state->nulls, state->nitems,
+ values, nulls, nitems);
+ }
+
+ PG_FREE_IF_COPY(v, 1);
+ }
+
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ array_intersection_finalfn(PG_FUNCTION_ARGS)
+ {
+ ArrayIntersectionState *state;
+ ArrayType *result;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ state = PG_ARGISNULL(0) ? NULL
+ : (ArrayIntersectionState *) PG_GETARG_POINTER(0);
+ if (state == NULL)
+ PG_RETURN_NULL();
+
+ type = get_type_cache(state->element_type, &fcinfo->flinfo->fn_extra);
+ result = construct_md_array(state->values, state->nulls,
+ 1, &state->nitems, &lbs, state->element_type,
+ type->typlen, type->typbyval, type->typalign);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ static void
+ check_concatenable(Oid element_type1, Oid element_type2)
+ {
+ if (element_type1 != element_type2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot concatenate incompatible arrays"),
+ errdetail("Arrays with element types %s and %s are not compatible for concatenation.",
+ format_type_be(element_type1),
+ format_type_be(element_type2))));
+ }
+
+ static void
+ check_comparable(Oid element_type1, Oid element_type2)
+ {
+ if (element_type1 != element_type2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot compare incompatible arrays"),
+ errdetail("Arrays with element types %s and %s are not compatible for comparison.",
+ format_type_be(element_type1),
+ format_type_be(element_type2))));
+ }
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 26966d2..83d8312 100644
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
*************** DATA(insert ( 2901 xmlconcat2 - 0
*** 222,227 ****
--- 222,229 ----
/* array */
DATA(insert ( 2335 array_agg_transfn array_agg_finalfn 0 2281 _null_ ));
+ DATA(insert ( 3160 array_fusion_transfn array_agg_finalfn 0 2281 _null_ ));
+ DATA(insert ( 3163 array_intersection_transfn array_intersection_finalfn 0 2281 _null_ ));
/* text */
DATA(insert ( 3538 string_agg_transfn string_agg_finalfn 0 2281 _null_ ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a859885..cb619bf 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2334 ( array_agg_fina
*** 1062,1067 ****
--- 1062,1101 ----
DESCR("array_agg final function");
DATA(insert OID = 2335 ( array_agg PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
DESCR("concatenate aggregate input into an array");
+ DATA(insert OID = 3147 ( cardinality PGNSP PGUID 12 1 0 0 f f f t f i 1 0 23 "2277" _null_ _null_ _null_ _null_ array_cardinality _null_ _null_ _null_ ));
+ DESCR("number of elements in array");
+ DATA(insert OID = 3148 ( trim_array PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 23" _null_ _null_ _null_ _null_ trim_array _null_ _null_ _null_ ));
+ DESCR("remove elements at end of array");
+ DATA(insert OID = 3149 ( array_trim PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 23" _null_ _null_ _null_ _null_ array_trim _null_ _null_ _null_ ));
+ DESCR("remove elements at end of array");
+ DATA(insert OID = 3150 ( array_sort PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ array_sort _null_ _null_ _null_ ));
+ DESCR("sort an array in ascending order");
+ DATA(insert OID = 3151 ( array_unique PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ array_unique _null_ _null_ _null_ ));
+ DESCR("remove duplicated values in an array");
+ DATA(insert OID = 3152 ( array_is_unique PGNSP PGUID 12 1 0 0 f f f t f i 1 0 16 "2277" _null_ _null_ _null_ _null_ array_is_unique _null_ _null_ _null_ ));
+ DESCR("no duplicated elements?");
+ DATA(insert OID = 3153 ( array_union PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_union _null_ _null_ _null_ ));
+ DESCR("concatenate two arrays and remove duplicated values");
+ DATA(insert OID = 3154 ( array_union_all PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_union_all _null_ _null_ _null_ ));
+ DESCR("concatenate two arrays");
+ DATA(insert OID = 3155 ( array_intersect PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_intersect _null_ _null_ _null_ ));
+ DESCR("intersection of two arrays and remove duplicated values");
+ DATA(insert OID = 3156 ( array_intersect_all PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_intersect_all _null_ _null_ _null_ ));
+ DESCR("intersection of two arrays");
+ DATA(insert OID = 3157 ( array_except PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_except _null_ _null_ _null_ ));
+ DESCR("subtraction of two arrays and remove duplicated values");
+ DATA(insert OID = 3158 ( array_except_all PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_except_all _null_ _null_ _null_ ));
+ DESCR("subtraction of two arrays");
+ DATA(insert OID = 3159 ( array_fusion_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 2277" _null_ _null_ _null_ _null_ array_fusion_transfn _null_ _null_ _null_ ));
+ DESCR("fusion transition function");
+ DATA(insert OID = 3160 ( array_fusion PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("concatenate aggregate arrays into an array");
+ DATA(insert OID = 3161 ( array_intersection_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 2277" _null_ _null_ _null_ _null_ array_intersection_transfn _null_ _null_ _null_ ));
+ DESCR("intersection transition function");
+ DATA(insert OID = 3162 ( array_intersection_finalfn PGNSP PGUID 12 1 0 0 f f f f f i 1 0 2277 "2281" _null_ _null_ _null_ _null_ array_intersection_finalfn _null_ _null_ _null_ ));
+ DESCR("intersection final function");
+ DATA(insert OID = 3163 ( array_intersection PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("intersection of all inputs");
DATA(insert OID = 760 ( smgrin PGNSP PGUID 12 1 0 0 f f f t f s 1 0 210 "2275" _null_ _null_ _null_ _null_ smgrin _null_ _null_ _null_ ));
DESCR("I/O");
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 8b7db79..cf7de13 100644
*** a/src/include/nodes/makefuncs.h
--- b/src/include/nodes/makefuncs.h
*************** extern TypeName *makeTypeNameFromOid(Oid
*** 72,77 ****
--- 72,78 ----
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype,
List *args, Oid collid, CoercionForm fformat);
+ extern FuncCall *makeFuncCall(List *funcname, List *args, int location);
extern DefElem *makeDefElem(char *name, Node *arg);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 7f7e744..725cc38 100644
*** a/src/include/utils/array.h
--- b/src/include/utils/array.h
*************** extern ArrayType *create_singleton_array
*** 281,285 ****
--- 281,300 ----
extern Datum array_agg_transfn(PG_FUNCTION_ARGS);
extern Datum array_agg_finalfn(PG_FUNCTION_ARGS);
+ extern Datum array_cardinality(PG_FUNCTION_ARGS);
+ extern Datum trim_array(PG_FUNCTION_ARGS);
+ extern Datum array_trim(PG_FUNCTION_ARGS);
+ extern Datum array_sort(PG_FUNCTION_ARGS);
+ extern Datum array_unique(PG_FUNCTION_ARGS);
+ extern Datum array_is_unique(PG_FUNCTION_ARGS);
+ extern Datum array_union(PG_FUNCTION_ARGS);
+ extern Datum array_union_all(PG_FUNCTION_ARGS);
+ extern Datum array_intersect(PG_FUNCTION_ARGS);
+ extern Datum array_intersect_all(PG_FUNCTION_ARGS);
+ extern Datum array_except(PG_FUNCTION_ARGS);
+ extern Datum array_except_all(PG_FUNCTION_ARGS);
+ extern Datum array_fusion_transfn(PG_FUNCTION_ARGS);
+ extern Datum array_intersection_transfn(PG_FUNCTION_ARGS);
+ extern Datum array_intersection_finalfn(PG_FUNCTION_ARGS);
#endif /* ARRAY_H */
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 7b05ce3..334121b 100644
*** a/src/test/regress/expected/arrays.out
--- b/src/test/regress/expected/arrays.out
*************** select * from t1;
*** 1558,1560 ****
--- 1558,1713 ----
[5:5]={"(42,43)"}
(1 row)
+ -- sort, unique, and set operations
+ SELECT cardinality(ARRAY[1, 2, 3]),
+ cardinality(ARRAY[[1, 2], [3, 4]]);
+ cardinality | cardinality
+ -------------+-------------
+ 3 | 4
+ (1 row)
+
+ SELECT trim_array(ARRAY[1, 2, 3], 0),
+ trim_array(ARRAY[1, 2, 3], 2),
+ array_trim('[-2:2]={-2,NULL,0,NULL,2}'::int[], 2),
+ array_trim(ARRAY[[1, 2], [3, 4]], 1);
+ trim_array | trim_array | array_trim | array_trim
+ ------------+------------+--------------------+------------
+ {1,2,3} | {1} | [-2:0]={-2,NULL,0} | {{1,2}}
+ (1 row)
+
+ SELECT trim_array(ARRAY[1, 2, 3], -1); -- ERROR
+ ERROR: number of trimmed elements (-1) must not be negative
+ SELECT trim_array(ARRAY[1, 2, 3], 4); -- ERROR
+ ERROR: number of trimmed elements (4) is greater than number of elements (3)
+ SELECT array_is_unique(ARRAY[1, 2, 3]);
+ array_is_unique
+ -----------------
+ t
+ (1 row)
+
+ SELECT array_is_unique(ARRAY['A', 'A', 'B']);
+ array_is_unique
+ -----------------
+ f
+ (1 row)
+
+ SELECT array_is_unique(ARRAY[1, NULL]),
+ array_is_unique(ARRAY[1, NULL, NULL]);
+ array_is_unique | array_is_unique
+ -----------------+-----------------
+ t | f
+ (1 row)
+
+ SELECT array_is_unique(ARRAY[[1]]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_union_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_union_all
+ --------------------------
+ {2,NULL,1,2,NULL,2,NULL}
+ (1 row)
+
+ SELECT array_union_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_union_all
+ -------------------
+ {2,NULL,1,2,NULL}
+ (1 row)
+
+ SELECT array_union(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_union
+ -------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT array_union(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_union
+ -------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT array_union(ARRAY[[1]], ARRAY[1]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_intersect_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_intersect_all
+ ---------------------
+ {2,NULL}
+ (1 row)
+
+ SELECT array_intersect_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_intersect_all
+ ---------------------
+
+ (1 row)
+
+ SELECT array_intersect(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_intersect
+ -----------------
+ {2,NULL}
+ (1 row)
+
+ SELECT array_intersect(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_intersect
+ -----------------
+
+ (1 row)
+
+ SELECT array_intersect(ARRAY[[1]], ARRAY[1]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_except_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_except_all
+ ------------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT array_except_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_except_all
+ -------------------
+ {1,2,2,NULL,NULL}
+ (1 row)
+
+ SELECT array_except(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ array_except
+ --------------
+ {1}
+ (1 row)
+
+ SELECT array_except(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ array_except
+ --------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT array_except(ARRAY[[1]], ARRAY[1]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_sort(ARRAY[1, 3, 2, 3, NULL, 1, NULL]),
+ array_sort(ARRAY['A', 'C', 'B', 'C', 'C', 'A']);
+ array_sort | array_sort
+ -----------------------+---------------
+ {1,1,2,3,3,NULL,NULL} | {A,A,B,C,C,C}
+ (1 row)
+
+ SELECT array_sort(ARRAY[[1]]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_unique(ARRAY[1, 3, 2, 3, NULL, 1, NULL]),
+ array_unique(ARRAY['A', 'C', 'B', 'C', 'C', 'A']);
+ array_unique | array_unique
+ --------------+--------------
+ {1,2,3,NULL} | {A,B,C}
+ (1 row)
+
+ SELECT array_unique(ARRAY[[1]]); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_fusion(a), array_intersection(a)
+ FROM (VALUES
+ (ARRAY[1, 2, 3, 2, 2]),
+ (ARRAY[1, 2, 4, 2]),
+ (ARRAY[NULL, 3, 2, 1])
+ ) AS t(a);
+ array_fusion | array_intersection
+ --------------------------------+--------------------
+ {1,2,3,2,2,1,2,4,2,NULL,3,2,1} | {1,2}
+ (1 row)
+
+ SELECT array_fusion(a) FROM (VALUES (ARRAY[[1]])) AS t(a); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
+ SELECT array_intersection(a) FROM (VALUES (ARRAY[[1]])) AS t(a); -- ERROR
+ ERROR: cannot use multi-dimensional arrays
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 9ea53b1..90d7aaa 100644
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
*************** insert into t1 (f1[5].q1) values(42);
*** 438,440 ****
--- 438,485 ----
select * from t1;
update t1 set f1[5].q2 = 43;
select * from t1;
+
+ -- sort, unique, and set operations
+
+ SELECT cardinality(ARRAY[1, 2, 3]),
+ cardinality(ARRAY[[1, 2], [3, 4]]);
+ SELECT trim_array(ARRAY[1, 2, 3], 0),
+ trim_array(ARRAY[1, 2, 3], 2),
+ array_trim('[-2:2]={-2,NULL,0,NULL,2}'::int[], 2),
+ array_trim(ARRAY[[1, 2], [3, 4]], 1);
+ SELECT trim_array(ARRAY[1, 2, 3], -1); -- ERROR
+ SELECT trim_array(ARRAY[1, 2, 3], 4); -- ERROR
+ SELECT array_is_unique(ARRAY[1, 2, 3]);
+ SELECT array_is_unique(ARRAY['A', 'A', 'B']);
+ SELECT array_is_unique(ARRAY[1, NULL]),
+ array_is_unique(ARRAY[1, NULL, NULL]);
+ SELECT array_is_unique(ARRAY[[1]]); -- ERROR
+ SELECT array_union_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_union_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_union(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_union(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_union(ARRAY[[1]], ARRAY[1]); -- ERROR
+ SELECT array_intersect_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_intersect_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_intersect(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_intersect(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_intersect(ARRAY[[1]], ARRAY[1]); -- ERROR
+ SELECT array_except_all(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_except_all(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_except(ARRAY[2, NULL, 1, 2, NULL], ARRAY[2, NULL]);
+ SELECT array_except(ARRAY[2, NULL, 1, 2, NULL], NULL);
+ SELECT array_except(ARRAY[[1]], ARRAY[1]); -- ERROR
+ SELECT array_sort(ARRAY[1, 3, 2, 3, NULL, 1, NULL]),
+ array_sort(ARRAY['A', 'C', 'B', 'C', 'C', 'A']);
+ SELECT array_sort(ARRAY[[1]]); -- ERROR
+ SELECT array_unique(ARRAY[1, 3, 2, 3, NULL, 1, NULL]),
+ array_unique(ARRAY['A', 'C', 'B', 'C', 'C', 'A']);
+ SELECT array_unique(ARRAY[[1]]); -- ERROR
+ SELECT array_fusion(a), array_intersection(a)
+ FROM (VALUES
+ (ARRAY[1, 2, 3, 2, 2]),
+ (ARRAY[1, 2, 4, 2]),
+ (ARRAY[NULL, 3, 2, 1])
+ ) AS t(a);
+ SELECT array_fusion(a) FROM (VALUES (ARRAY[[1]])) AS t(a); -- ERROR
+ SELECT array_intersection(a) FROM (VALUES (ARRAY[[1]])) AS t(a); -- ERROR