v16-0002-general-purpose-array_sort.patch

From 6dd5e67dd046edbc3ec6c4ec65975ab3e5f041d8 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 9 Mar 2025 20:55:25 +0800
Subject: [PATCH v16 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 e24ef42ad98..8c3c8df5f36 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20674,7 +20674,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>
@@ -20682,6 +20685,13 @@ SELECT NULLIF(value, '(none)') ...
         The sort order is determined by the <literal>&lt;</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 b6c4f4c2786..c7c169366e3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1753,6 +1753,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 9844d5b81fa..3f82972cdfe 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2771,6 +2771,44 @@ SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[] COLLATE "C");
  {Abc,CCC,bar,bbc,foo,NULL}
 (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 5a5549c8e0a..bc19d84e2cf 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -843,6 +843,16 @@ 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");
 
+-- 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