hi.
before commit 4618045bee4a6d3efcb489c319649d8dd9aaa738 ([0])
select array_sort(array(select '1 4'::int2vector union all select '1
2'::int2vector));
array_sort
--------------------------
[1:2][0:1]={{1,2},{1,4}}
(1 row)
after
select array_sort(array(select '1 4'::int2vector union all select '1
2'::int2vector));
array_sort
---------------
{"1 2","1 4"}
(1 row)
now look closer, the second is the expected result...
I didn't dig deeper why commit 4618045bee made this patch result correct,
but I guess it would be best to include such a test case,
so I've attached a patch.
[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=4618045bee4a6d3efcb489c319649d8dd9aaa738
From 9ab98e6470dd5f16ef6024c291e5aa99ebfbecde Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sun, 16 Mar 2025 21:31:55 +0800
Subject: [PATCH v17 2/2] general purpose array_sort
Add two arguments to array_sort:
1. is_ascending: If true, then the array will be sorted in ascending order,
otherwise in descending order.
2. nulls_first: If true, null values will appear
before non-null values, otherwise, null values will appear after non-null
values.
discussion: https://postgr.es/m/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw%40mail.gmail.com
---
doc/src/sgml/func.sgml | 12 +++++++-
src/backend/utils/adt/array_userfuncs.c | 36 +++++++++++++++++++----
src/include/catalog/pg_proc.dat | 9 ++++++
src/test/regress/expected/arrays.out | 38 +++++++++++++++++++++++++
src/test/regress/sql/arrays.sql | 10 +++++++
5 files changed, 99 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6d901230bdc..c03a66ffb26 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20708,7 +20708,10 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>array_sort</primary>
</indexterm>
- <function>array_sort</function> ( <type>anyarray</type> )
+ <function>array_sort</function> (
+ <parameter>array</parameter> <type>anyarray</type>
+ <optional>, <parameter>is_ascending</parameter> <type>boolean</type>
+ <optional>, <parameter>nulls_first</parameter> <type>boolean</type></optional></optional>)
<returnvalue>anyarray</returnvalue>
</para>
<para>
@@ -20716,6 +20719,13 @@ SELECT NULLIF(value, '(none)') ...
The sort order is determined by the <literal><</literal> operator of the element type,
nulls will appear after non-null values.
The collation to use can be forced by adding a <literal>COLLATE</literal> clause to any of the arguments.
+ </para>
+ <para>
+ If <parameter>is_ascending</parameter> is true then sort by ascending order, otherwise descending order.
+ <parameter>is_ascending</parameter> defaults to true.
+ If <parameter>nulls_first</parameter> is true then nulls appear before non-null values,
+ otherwise nulls appear after non-null values.
+ <parameter>nulls_first</parameter> defaults to the opposite of <parameter>is_ascending</parameter> if not provided.
</para>
<para>
<literal>array_sort(ARRAY[[2,4],[2,1],[6,5]])</literal>
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 583e56fc805..2e38ef05e48 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -1877,7 +1877,6 @@ array_reverse(PG_FUNCTION_ARGS)
* array_sort
*
* Sorts the first dimension of the array.
- * The sort order is determined by the "<" operator of the element type.
*/
Datum
array_sort(PG_FUNCTION_ARGS)
@@ -1896,11 +1895,24 @@ array_sort(PG_FUNCTION_ARGS)
int ndim,
*dims,
*lbs;
+ bool is_ascending = true;
+ bool nulls_first = false;
ndim = ARR_NDIM(array);
dims = ARR_DIMS(array);
lbs = ARR_LBOUND(array);
+ if (PG_NARGS() > 1)
+ {
+ is_ascending = PG_GETARG_BOOL(1);
+
+ /*
+ * If nulls_first not provided, it defaults to the opposite of
+ * is_ascending.
+ */
+ nulls_first = PG_NARGS() > 2 ? PG_GETARG_BOOL(2) : !is_ascending;
+ }
+
elmtyp = ARR_ELEMTYPE(array);
cache_info = (ArraySortCachedInfo *) fcinfo->flinfo->fn_extra;
if (cache_info == NULL)
@@ -1918,8 +1930,10 @@ array_sort(PG_FUNCTION_ARGS)
typentry = cache_info->typentry;
if (typentry == NULL || typentry->type_id != elmtyp)
{
- typentry = lookup_type_cache(elmtyp, TYPECACHE_LT_OPR);
- if (!OidIsValid(typentry->lt_opr))
+ typentry = lookup_type_cache(elmtyp,
+ is_ascending ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR);
+ if ((is_ascending && !OidIsValid(typentry->lt_opr)) ||
+ (!is_ascending && !OidIsValid(typentry->gt_opr)))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("could not identify ordering operator for type %s",
@@ -1964,9 +1978,9 @@ array_sort(PG_FUNCTION_ARGS)
PG_RETURN_ARRAYTYPE_P(array);
tuplesortstate = tuplesort_begin_datum(typentry->type_id,
- typentry->lt_opr,
+ is_ascending ? typentry->lt_opr : typentry->gt_opr,
collation,
- false, work_mem, NULL, false);
+ nulls_first, work_mem, NULL, false);
array_iterator = array_create_iterator(array, ndim - 1, &cache_info->array_meta);
while (array_iterate(array_iterator, &value, &isnull))
@@ -2001,3 +2015,15 @@ array_sort(PG_FUNCTION_ARGS)
PG_FREE_IF_COPY(array, 0);
PG_RETURN_DATUM(makeArrayResultAny(astate, CurrentMemoryContext, true));
}
+
+Datum
+array_sort_order(PG_FUNCTION_ARGS)
+{
+ return array_sort(fcinfo);
+}
+
+Datum
+array_sort_order_nulls_first(PG_FUNCTION_ARGS)
+{
+ return array_sort(fcinfo);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 90ce03355c2..646425cf7cb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1775,6 +1775,15 @@
{ oid => '8810', descr => 'sort array',
proname => 'array_sort', prorettype => 'anyarray',
proargtypes => 'anyarray', prosrc => 'array_sort'},
+{ oid => '8811', descr => 'sort array',
+ proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+ proargtypes => 'anyarray bool', proargnames => '{array,is_ascending}',
+ prosrc => 'array_sort_order'},
+{ oid => '8812', descr => 'sort array',
+ proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+ proargtypes => 'anyarray bool bool',
+ proargnames => '{array,is_ascending, nulls_first}',
+ prosrc => 'array_sort_order_nulls_first'},
{ oid => '3816', descr => 'array typanalyze',
proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 089cb8bc443..26346083f31 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2903,6 +2903,44 @@ SELECT array_sort(ARRAY(SELECT '1 4'::int2vector UNION ALL SELECT '1 2'::int2vec
{"1 2","1 4"}
(1 row)
+-- array_sort with order specified
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true);
+ array_sort
+--------------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6,NULL}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false);
+ array_sort
+--------------------------------
+ {NULL,6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+-- array_sort with order and nullsfirst flag specified
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true, true);
+ array_sort
+--------------------------------
+ {NULL,1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true, false);
+ array_sort
+--------------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6,NULL}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false, true);
+ array_sort
+--------------------------------
+ {NULL,6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false, false);
+ array_sort
+--------------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1,NULL}
+(1 row)
+
-- multidimensional array tests
SELECT array_sort('{{1}}'::int[]);
array_sort
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 9161c3aedc2..788f004945a 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -866,6 +866,16 @@ SELECT array_sort('{foo,bar,CCC,Abc,bbc}'::text[] COLLATE "C");
SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[] COLLATE "C");
SELECT array_sort(ARRAY(SELECT '1 4'::int2vector UNION ALL SELECT '1 2'::int2vector));
+-- array_sort with order specified
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true);
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false);
+
+-- array_sort with order and nullsfirst flag specified
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true, true);
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], true, false);
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false, true);
+SELECT array_sort('{1.1,3.3,5.5,2.2,null,4.4,6.6}'::float8[], false, false);
+
-- multidimensional array tests
SELECT array_sort('{{1}}'::int[]);
SELECT array_sort(ARRAY[[2,4],[2,1],[6,5]]);
--
2.34.1
From 7b0fc8280dd24814cfb7bab76bd0462017c2cc15 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sun, 16 Mar 2025 21:40:38 +0800
Subject: [PATCH v17 1/2] general purpose array_sort
Introduce the SQL-callable function array_sort(anyarray). The parameter passed
to this function cannot truly be a polymorphic data type. Instead, it accepts
any array type that supports the "less than" (`<`) operator.
If the input parameter is a multidimensional array, array_sort will sort based
on the first dimension. By default, sorting is performed based on
the argument's collation. However, you can also specify a collation clause if
needed, for special value NULL: nulls will appear after non-null values.
for example:
SELECT array_sort('{foo,bar,CCC,Abc,bbc}'::text[] COLLATE "C");
will sort based on "C" collation.
Author: Junwang Zhao <zhjw...@gmail.com>
Co-authored-by: Jian He <jian.universal...@gmail.com>
Reviewed-by:
Michael Paquier <mich...@paquier.xyz>
Aleksander Alekseev <aleksan...@timescale.com>,
Tom Lane <t...@sss.pgh.pa.us>,
David G. Johnston <david.g.johns...@gmail.com>,
Amit Langote <amitlangot...@gmail.com>,
andr...@proxel.se <andr...@proxel.se>,
Robert Haas <robertmh...@gmail.com>,
Dean Rasheed <dean.a.rash...@gmail.com>
discussion: https://postgr.es/m/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw%40mail.gmail.com
---
doc/src/sgml/func.sgml | 20 +++
src/backend/utils/adt/array_userfuncs.c | 143 ++++++++++++++++++
src/backend/utils/adt/arrayfuncs.c | 3 +-
src/include/catalog/pg_proc.dat | 3 +
src/include/utils/array.h | 1 +
src/test/regress/expected/arrays.out | 102 +++++++++++++
.../regress/expected/collate.icu.utf8.out | 13 ++
src/test/regress/sql/arrays.sql | 27 ++++
src/test/regress/sql/collate.icu.utf8.sql | 4 +
src/tools/pgindent/typedefs.list | 1 +
10 files changed, 316 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1c3810e1a04..6d901230bdc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20703,6 +20703,26 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>array_sort</primary>
+ </indexterm>
+ <function>array_sort</function> ( <type>anyarray</type> )
+ <returnvalue>anyarray</returnvalue>
+ </para>
+ <para>
+ Sorts the first dimension of the array.
+ The sort order is determined by the <literal><</literal> operator of the element type,
+ nulls will appear after non-null values.
+ The collation to use can be forced by adding a <literal>COLLATE</literal> clause to any of the arguments.
+ </para>
+ <para>
+ <literal>array_sort(ARRAY[[2,4],[2,1],[6,5]])</literal>
+ <returnvalue>{{2,1},{2,4},{6,5}}</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm id="function-array-to-string">
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 2aae2f8ed93..583e56fc805 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -15,6 +15,7 @@
#include "catalog/pg_type.h"
#include "common/int.h"
#include "common/pg_prng.h"
+#include "miscadmin.h"
#include "libpq/pqformat.h"
#include "nodes/supportnodes.h"
#include "port/pg_bitutils.h"
@@ -22,6 +23,7 @@
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
+#include "utils/tuplesort.h"
#include "utils/typcache.h"
/*
@@ -43,6 +45,18 @@ typedef struct DeserialIOData
Oid typioparam;
} DeserialIOData;
+/*
+ * ArraySortCachedInfo
+ * Used for caching data in array_sort
+ */
+typedef struct ArraySortCachedInfo
+{
+ TypeCacheEntry *typentry; /* type cache entry for element type */
+ TypeCacheEntry *array_typentry; /* type cache entry for array type */
+ ArrayMetaState array_meta; /* array metadata for better
+ * array_create_iterator performance */
+} ArraySortCachedInfo;
+
static Datum array_position_common(FunctionCallInfo fcinfo);
@@ -1858,3 +1872,132 @@ array_reverse(PG_FUNCTION_ARGS)
PG_RETURN_ARRAYTYPE_P(result);
}
+
+/*
+ * array_sort
+ *
+ * Sorts the first dimension of the array.
+ * The sort order is determined by the "<" operator of the element type.
+ */
+Datum
+array_sort(PG_FUNCTION_ARGS)
+{
+ ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
+ Oid elmtyp;
+ Oid array_type = InvalidOid;
+ Oid collation = PG_GET_COLLATION();
+ ArraySortCachedInfo *cache_info;
+ TypeCacheEntry *typentry;
+ Tuplesortstate *tuplesortstate;
+ ArrayIterator array_iterator;
+ Datum value;
+ bool isnull;
+ ArrayBuildStateAny *astate = NULL;
+ int ndim,
+ *dims,
+ *lbs;
+
+ ndim = ARR_NDIM(array);
+ dims = ARR_DIMS(array);
+ lbs = ARR_LBOUND(array);
+
+ elmtyp = ARR_ELEMTYPE(array);
+ cache_info = (ArraySortCachedInfo *) fcinfo->flinfo->fn_extra;
+ if (cache_info == NULL)
+ {
+ cache_info = (ArraySortCachedInfo *) MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(ArraySortCachedInfo));
+ cache_info->typentry = NULL;
+ cache_info->array_typentry = NULL;
+ fcinfo->flinfo->fn_extra = (void *) cache_info;
+ }
+
+ if (ndim == 1)
+ {
+ /* Finds the ordering operator for the type for 1-D arrays */
+ typentry = cache_info->typentry;
+ if (typentry == NULL || typentry->type_id != elmtyp)
+ {
+ typentry = lookup_type_cache(elmtyp, TYPECACHE_LT_OPR);
+ if (!OidIsValid(typentry->lt_opr))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("could not identify ordering operator for type %s",
+ format_type_be(elmtyp)));
+
+ cache_info->typentry = typentry;
+ cache_info->array_meta.element_type = elmtyp;
+ cache_info->array_meta.typlen = typentry->typlen;
+ cache_info->array_meta.typbyval = typentry->typbyval;
+ cache_info->array_meta.typalign = typentry->typalign;
+ }
+ }
+ else
+ {
+ /* Finds the ordering operator for the array type for multi-D arrays */
+ typentry = cache_info->array_typentry;
+ if (typentry == NULL || typentry->typelem != elmtyp)
+ {
+ array_type = get_array_type(elmtyp);
+ if (!OidIsValid(array_type))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("could not find array type for data type %s",
+ format_type_be(elmtyp)));
+
+ typentry = lookup_type_cache(array_type, TYPECACHE_LT_OPR);
+ if (!OidIsValid(typentry->lt_opr))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("could not identify ordering operator for type %s",
+ format_type_be(array_type)));
+ cache_info->array_typentry = typentry;
+ }
+ cache_info->array_meta.element_type = elmtyp;
+ get_typlenbyvalalign(elmtyp,
+ &cache_info->array_meta.typlen,
+ &cache_info->array_meta.typbyval,
+ &cache_info->array_meta.typalign);
+ }
+
+ if (ndim < 1 || dims[0] < 2)
+ PG_RETURN_ARRAYTYPE_P(array);
+
+ tuplesortstate = tuplesort_begin_datum(typentry->type_id,
+ typentry->lt_opr,
+ collation,
+ false, work_mem, NULL, false);
+
+ array_iterator = array_create_iterator(array, ndim - 1, &cache_info->array_meta);
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ tuplesort_putdatum(tuplesortstate, value, isnull);
+ }
+ array_free_iterator(array_iterator);
+
+ /*
+ * Do the sort.
+ */
+ tuplesort_performsort(tuplesortstate);
+
+ while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
+ {
+ astate = accumArrayResultAny(astate, value, isnull,
+ typentry->type_id, CurrentMemoryContext);
+ }
+
+ tuplesort_end(tuplesortstate);
+
+ /*
+ * accumArrayResultAny will set the first dimension lower bound to 1, we
+ * need restore it. see accumArrayResultArr.
+ */
+ if (ndim == 1)
+ astate->scalarstate->lb = lbs[0];
+ else
+ astate->arraystate->lbs[0] = lbs[0];
+
+ /* Avoid leaking memory when handed toasted input */
+ PG_FREE_IF_COPY(array, 0);
+ PG_RETURN_DATUM(makeArrayResultAny(astate, CurrentMemoryContext, true));
+}
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index c8f53c6fbe7..b8062e64802 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -5330,6 +5330,7 @@ initArrayResultWithSize(Oid element_type, MemoryContext rcontext,
MemoryContextAlloc(arr_context, astate->alen * sizeof(bool));
astate->nelems = 0;
astate->element_type = element_type;
+ astate->lb = 1; /* default lower bound */
get_typlenbyvalalign(element_type,
&astate->typlen,
&astate->typbyval,
@@ -5869,7 +5870,7 @@ makeArrayResultAny(ArrayBuildStateAny *astate,
/* If no elements were presented, we want to create an empty array */
ndims = (astate->scalarstate->nelems > 0) ? 1 : 0;
dims[0] = astate->scalarstate->nelems;
- lbs[0] = 1;
+ lbs[0] = astate->scalarstate->lb;
result = makeMdArrayResult(astate->scalarstate, ndims, dims, lbs,
rcontext, release);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 890822eaf79..90ce03355c2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1772,6 +1772,9 @@
{ oid => '8686', descr => 'reverse array',
proname => 'array_reverse', prorettype => 'anyarray',
proargtypes => 'anyarray', prosrc => 'array_reverse' },
+{ oid => '8810', descr => 'sort array',
+ proname => 'array_sort', prorettype => 'anyarray',
+ proargtypes => 'anyarray', prosrc => 'array_sort'},
{ oid => '3816', descr => 'array typanalyze',
proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 52f1fbf8d43..ce0ef4c348c 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -192,6 +192,7 @@ typedef struct ArrayBuildState
int alen; /* allocated length of above arrays */
int nelems; /* number of valid entries in above arrays */
Oid element_type; /* data type of the Datums */
+ int lb; /* lower bound for one dimension array */
int16 typlen; /* needed info about datatype */
bool typbyval;
char typalign;
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 7afd7356bbe..089cb8bc443 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2860,3 +2860,105 @@ SELECT array_reverse('{{1,2},{3,4},{5,6},{7,8}}'::int[]);
{{7,8},{5,6},{3,4},{1,2}}
(1 row)
+-- array_sort
+SELECT array_sort('{}'::int[]);
+ array_sort
+------------
+ {}
+(1 row)
+
+SELECT array_sort('{1}'::int[]);
+ array_sort
+------------
+ {1}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+ array_sort
+---------------
+ {1,2,3,4,5,6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+ array_sort
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{foo,bar,CCC,Abc,bbc}'::text[] COLLATE "C");
+ array_sort
+-----------------------
+ {Abc,CCC,bar,bbc,foo}
+(1 row)
+
+SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[] COLLATE "C");
+ array_sort
+----------------------------
+ {Abc,CCC,bar,bbc,foo,NULL}
+(1 row)
+
+SELECT array_sort(ARRAY(SELECT '1 4'::int2vector UNION ALL SELECT '1 2'::int2vector));
+ array_sort
+---------------
+ {"1 2","1 4"}
+(1 row)
+
+-- multidimensional array tests
+SELECT array_sort('{{1}}'::int[]);
+ array_sort
+------------
+ {{1}}
+(1 row)
+
+SELECT array_sort(ARRAY[[2,4],[2,1],[6,5]]);
+ array_sort
+---------------------
+ {{2,1},{2,4},{6,5}}
+(1 row)
+
+SELECT array_sort('{{"1 2","3 4"}, {"1 -2","-1 4"}}'::int2vector[]);
+ array_sort
+---------------------------------
+ {{"1 -2","-1 4"},{"1 2","3 4"}}
+(1 row)
+
+-- no ordering operator tests
+SELECT array_sort('{1}'::xid[]); -- ndim < 1
+ERROR: could not identify ordering operator for type xid
+SELECT array_sort('{1,2,3}'::xid[]);
+ERROR: could not identify ordering operator for type xid
+SELECT array_sort('{{1,2,3}}'::xid[]); -- dims[0] < 2
+ERROR: could not identify ordering operator for type xid[]
+SELECT array_sort('{{1,2,3},{2,3,4}}'::xid[]);
+ERROR: could not identify ordering operator for type xid[]
+-- bounds preservation tests
+SELECT array_sort(a) FROM (VALUES ('[10:12][20:21]={{1,2},{10,20},{3,4}}'::int[])) v(a);
+ array_sort
+--------------------------------------
+ [10:12][20:21]={{1,2},{3,4},{10,20}}
+(1 row)
+
+SELECT array_sort(a) FROM (VALUES ('[-1:0]={7,1}'::int[])) v(a);
+ array_sort
+--------------
+ [-1:0]={1,7}
+(1 row)
+
+SELECT array_sort(a) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
+ array_sort
+--------------------------------------
+ [-2:0][20:21]={{1,-4},{1,2},{10,20}}
+(1 row)
+
+SELECT array_sort(a [-1:0]) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
+ array_sort
+------------------
+ {{1,-4},{10,20}}
+(1 row)
+
+SELECT array_sort(a [-1:0][20:20]) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
+ array_sort
+------------
+ {{1},{10}}
+(1 row)
+
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index aee4755c083..69805d4b9ec 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1471,6 +1471,19 @@ SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_inse
t | t
(1 row)
+-- tests with array_sort
+SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
+ array_sort
+------------
+ {a,B}
+(1 row)
+
+SELECT array_sort('{a,B}'::text[] COLLATE "C");
+ array_sort
+------------
+ {B,a}
+(1 row)
+
-- test language tags
CREATE COLLATION lt_insensitive (provider = icu, locale = 'en-u-ks-level1', deterministic = false);
SELECT 'aBcD' COLLATE lt_insensitive = 'AbCd' COLLATE lt_insensitive;
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 399a0797f3b..9161c3aedc2 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -856,3 +856,30 @@ SELECT array_reverse('{1}'::int[]);
SELECT array_reverse('{1,2}'::int[]);
SELECT array_reverse('{1,2,3,NULL,4,5,6}'::int[]);
SELECT array_reverse('{{1,2},{3,4},{5,6},{7,8}}'::int[]);
+
+-- array_sort
+SELECT array_sort('{}'::int[]);
+SELECT array_sort('{1}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+SELECT array_sort('{foo,bar,CCC,Abc,bbc}'::text[] COLLATE "C");
+SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[] COLLATE "C");
+SELECT array_sort(ARRAY(SELECT '1 4'::int2vector UNION ALL SELECT '1 2'::int2vector));
+
+-- multidimensional array tests
+SELECT array_sort('{{1}}'::int[]);
+SELECT array_sort(ARRAY[[2,4],[2,1],[6,5]]);
+SELECT array_sort('{{"1 2","3 4"}, {"1 -2","-1 4"}}'::int2vector[]);
+
+-- no ordering operator tests
+SELECT array_sort('{1}'::xid[]); -- ndim < 1
+SELECT array_sort('{1,2,3}'::xid[]);
+SELECT array_sort('{{1,2,3}}'::xid[]); -- dims[0] < 2
+SELECT array_sort('{{1,2,3},{2,3,4}}'::xid[]);
+
+-- bounds preservation tests
+SELECT array_sort(a) FROM (VALUES ('[10:12][20:21]={{1,2},{10,20},{3,4}}'::int[])) v(a);
+SELECT array_sort(a) FROM (VALUES ('[-1:0]={7,1}'::int[])) v(a);
+SELECT array_sort(a) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
+SELECT array_sort(a [-1:0]) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
+SELECT array_sort(a [-1:0][20:20]) FROM (VALUES ('[-2:0][20:21]={{1,2},{10,20},{1,-4}}'::int[])) v(a);
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 38ebcd99508..dbc190227d0 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -564,6 +564,10 @@ CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=second
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_insensitive;
+-- tests with array_sort
+SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
+SELECT array_sort('{a,B}'::text[] COLLATE "C");
+
-- test language tags
CREATE COLLATION lt_insensitive (provider = icu, locale = 'en-u-ks-level1', deterministic = false);
SELECT 'aBcD' COLLATE lt_insensitive = 'AbCd' COLLATE lt_insensitive;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 93339ef3c58..cdde69faced 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -151,6 +151,7 @@ ArrayIOData
ArrayIterator
ArrayMapState
ArrayMetaState
+ArraySortCachedInfo
ArraySubWorkspace
ArrayToken
ArrayType
--
2.34.1
