diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 095b02c3b2..d485fc3b0a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20426,13 +20426,25 @@ SELECT NULLIF(value, '(none)') ... array_sort - array_sort ( anyarray , dir ) + array_sort ( anyarray COLLATE collation_name + , sort_asc boolean + , nulls_first boolean + ) anyarray + - Sorts the array in either ascending or descending order. - dir must be asc - or desc. The array must be empty or one-dimensional. + Sorts the array based on the given parameter. + + + If the COLLATE option is specified then sorting is based on collation_name, otherwise + using array element type's collation. + If sort_asc is true then sort by ascending order, otherwise descending order. + sort_asc defaults to true. + If nulls_first is true then nulls appear before non-null values, false means nulls appear after non-null values. + nulls_first defaults to true when sort_asc is false, + nulls_first defaults to false when sort_asc is true. + The array cannot be multidimensional. array_sort(ARRAY[1,2,5,6,3,4]) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index b0d0de051e..454691f39a 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -498,6 +498,14 @@ LANGUAGE INTERNAL STRICT IMMUTABLE PARALLEL SAFE AS 'make_interval'; +CREATE OR REPLACE FUNCTION + array_sort(src_array anyarray, sort_asc boolean DEFAULT true, + nulls_first boolean DEFAULT false) +RETURNS anyarray +LANGUAGE INTERNAL +STRICT VOLATILE +AS 'array_sort'; + CREATE OR REPLACE FUNCTION jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, create_if_missing boolean DEFAULT true) diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c index 841fe435e1..b8fa7c07e7 100644 --- a/src/backend/utils/adt/array_userfuncs.c +++ b/src/backend/utils/adt/array_userfuncs.c @@ -1699,12 +1699,10 @@ Datum array_sort(PG_FUNCTION_ARGS) { ArrayType *array = PG_GETARG_ARRAYTYPE_P(0); - text *dirstr = (fcinfo->nargs > 1) ? PG_GETARG_TEXT_PP(1) : NULL; - int32 dc = (dirstr) ? VARSIZE_ANY_EXHDR(dirstr) : 0; - char *d = (dirstr) ? VARDATA_ANY(dirstr) : NULL; + bool sort_asc = true; + bool nulls_first = true; Oid elmtyp; Oid collation = PG_GET_COLLATION(); - int dir = -1; TypeCacheEntry *typentry; Tuplesortstate *tuplesortstate; ArrayIterator array_iterator; @@ -1715,49 +1713,43 @@ array_sort(PG_FUNCTION_ARGS) if (ARR_NDIM(array) > 1) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("multidimensional arrays are not supported"))); + errmsg("multidimensional arrays sorting are not supported"))); if (ARR_NDIM(array) < 1) PG_RETURN_ARRAYTYPE_P(array); + /* by default, we sort array by asc, nulls last */ + if (PG_NARGS() >= 2) + { + sort_asc = PG_GETARG_BOOL(1); + if (sort_asc) + nulls_first = false; + } - if (ARR_HASNULL(array)) - ereport(ERROR, - (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), - errmsg("array must not contain nulls"))); - - if (dirstr == NULL || (dc == 3 - && (d[0] == 'a' || d[0] == 'A') - && (d[1] == 's' || d[1] == 'S') - && (d[2] == 'c' || d[2] == 'C'))) - dir = 1; - else if (dc == 4 - && (d[0] == 'd' || d[0] == 'D') - && (d[1] == 'e' || d[1] == 'E') - && (d[2] == 's' || d[2] == 'S') - && (d[3] == 'c' || d[3] == 'C')) - dir = 0; - if (dir == -1) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("second parameter must be \"ASC\" or \"DESC\""))); + if (PG_NARGS() >= 3) + nulls_first = PG_GETARG_BOOL(2); elmtyp = ARR_ELEMTYPE(array); typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra; if (typentry == NULL || typentry->type_id != elmtyp) { - typentry = lookup_type_cache(elmtyp, dir == 1 ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR); + typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR); + if ((sort_asc && typentry->lt_opr == InvalidOid) || + (!sort_asc && typentry->gt_opr == InvalidOid)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("type %s has no default btree operator class", + format_type_be(elmtyp)))); fcinfo->flinfo->fn_extra = (void *) typentry; } tuplesortstate = tuplesort_begin_datum(elmtyp, - dir == 1 ? typentry->lt_opr : typentry->gt_opr, + sort_asc ? typentry->lt_opr : typentry->gt_opr, collation, - false, work_mem, NULL, false); + nulls_first, work_mem, NULL, false); array_iterator = array_create_iterator(array, 0, NULL); while (array_iterate(array_iterator, &value, &isnull)) { - Assert(!isnull); tuplesort_putdatum(tuplesortstate, value, isnull); } array_free_iterator(array_iterator); @@ -1769,8 +1761,7 @@ array_sort(PG_FUNCTION_ARGS) while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL)) { - Assert(!isnull); - astate = accumArrayResult(astate, value, false, + astate = accumArrayResult(astate, value, isnull, elmtyp, CurrentMemoryContext); } @@ -1781,9 +1772,3 @@ array_sort(PG_FUNCTION_ARGS) PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext)); } -Datum -array_sort_order(PG_FUNCTION_ARGS) -{ - return array_sort(fcinfo); -} - diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index b5385fbefe..c40dd2d7e3 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1736,10 +1736,7 @@ proargtypes => 'anyarray int4', prosrc => 'array_sample' }, { oid => '8810', descr => 'sort array', proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray', - proargtypes => 'anyarray', prosrc => 'array_sort'}, -{ oid => '8811', descr => 'sort array', - proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray', - proargtypes => 'anyarray text', prosrc => 'array_sort_order'}, + proargtypes => 'anyarray bool bool', prosrc => 'array_sort'}, { oid => '3816', descr => 'array typanalyze', proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool', proargtypes => 'internal', prosrc => 'array_typanalyze' }, diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 31345295c1..032977e790 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -1338,6 +1338,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, null, null}'::text[] collate case_insensitive, false, false); + array_sort +----------------- + {B,a,NULL,NULL} +(1 row) + +SELECT array_sort('{a,B, null, null}'::text[] collate case_sensitive, false, false); + array_sort +----------------- + {B,a,NULL,NULL} +(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 3bb13b49de..99b3abe56f 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -830,8 +830,9 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail -- array_sort SELECT array_sort('{}'::int[]); -SELECT array_sort('{1,3,5,2,4,6}'::int[]); -SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc'); +SELECT array_sort('{{1}}'::int[]); +SELECT array_sort('{1,3,5,2,4,6}'::int[] COLLATE "pg_c_utf8"); +SELECT array_sort('{1,3,5,2,4,6}'::int[], false); SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc'); SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc'); SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]); @@ -840,3 +841,10 @@ SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,DŽxxDŽ džxxDž Džxxdž,ȺȺ SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,DŽxxDŽ džxxDž Džxxdž,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc'); SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,DŽxxDŽ džxxDž Džxxdž,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc'); SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,DŽxxDŽ džxxDž Džxxdž,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc'); +SELECT array_sort('{a,B, null, null}'::text[]); +SELECT array_sort('{a,B, null, null}'::text[]) = + array_sort('{a,B, null, null}'::text[], true, false) as expect_true; +SELECT array_sort('{a,B, null, null}'::text[], false); +SELECT array_sort('{a,B, null, null}'::text[], true, false); +SELECT array_sort('{a,B, null, null}'::text[], true, true); +SELECT array_sort('{a,B, null, null}'::text[], false, true); \ No newline at end of file diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index 80f28a97d7..b0190196ff 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -536,6 +536,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, null, null}'::text[] collate case_insensitive, false, false); +SELECT array_sort('{a,B, null, null}'::text[] collate case_sensitive, false, false); + -- 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;