array_sort_changes.no-cfbot
application/octet-stream
Filename: array_sort_changes.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: general purpose array_sort
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)') ...
<indexterm>
<primary>array_sort</primary>
</indexterm>
- <function>array_sort</function> ( <type>anyarray</type> <optional>, <parameter>dir</parameter> </optional>)
+ <function>array_sort</function> ( <type>anyarray</type> <optional> COLLATE <replaceable>collation_name</replaceable> </optional>
+ <optional>, <parameter>sort_asc</parameter> <type>boolean</type>
+ <optional>, <parameter>nulls_first</parameter> <type>boolean</type>
+ </optional></optional>)
<returnvalue>anyarray</returnvalue>
</para>
+
<para>
- Sorts the array in either ascending or descending order.
- <parameter>dir</parameter> must be <literal>asc</literal>
- or <literal>desc</literal>. The array must be empty or one-dimensional.
+ Sorts the array based on the given parameter.
+ </para>
+ <para>
+ If the <literal>COLLATE</literal> option is specified then sorting is based on <replaceable>collation_name</replaceable>, otherwise
+ using array element type's collation.
+ If <parameter>sort_asc</parameter> is true then sort by ascending order, otherwise descending order.
+ <parameter>sort_asc</parameter> defaults to true.
+ If <parameter>nulls_first</parameter> is true then nulls appear before non-null values, false means nulls appear after non-null values.
+ <parameter>nulls_first</parameter> defaults to true when <parameter>sort_asc</parameter> is false,
+ <parameter>nulls_first</parameter> defaults to false when <parameter>sort_asc</parameter> is true.
+ The array cannot be multidimensional.
</para>
<para>
<literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
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;