From d74ba41d298eaf7f790a1ed4c4e9702ebb519aeb Mon Sep 17 00:00:00 2001 From: Junwang Zhao Date: Sat, 26 Oct 2024 03:53:17 +0000 Subject: [PATCH v13 2/2] support sort order and nullsfirst flag Signed-off-by: Junwang Zhao --- doc/src/sgml/func.sgml | 6 +++- src/backend/utils/adt/array_userfuncs.c | 42 ++++++++++++++++++++----- src/include/catalog/pg_proc.dat | 6 ++++ src/test/regress/expected/arrays.out | 38 ++++++++++++++++++++++ src/test/regress/sql/arrays.sql | 10 ++++++ 5 files changed, 94 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 748611dee5..afbda78688 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20443,12 +20443,16 @@ SELECT NULLIF(value, '(none)') ... array_sort - array_sort ( anyarray ) + array_sort ( anyarray , is_ascending boolean , nulls_first boolean) anyarray Sorts the first dimension of the array. The sort order is determined by the < operator of the element type, nulls will appear after non-null values. The collation to use can be forced by adding a COLLATE clause to any of the arguments. + If is_ascending is true then sort by ascending order, otherwise descending order. + is_ascending defaults to true. + If nulls_first is true then nulls appear before non-null values, otherwise nulls appear after non-null values. + nulls_first defaults to the opposite of is_ascending if not provided. array_sort(ARRAY[[2,4],[2,1],[6,5]]) diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c index 3a50e5ca97..6e5125b46e 100644 --- a/src/backend/utils/adt/array_userfuncs.c +++ b/src/backend/utils/adt/array_userfuncs.c @@ -1816,7 +1816,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) @@ -1835,6 +1834,8 @@ 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); @@ -1843,6 +1844,17 @@ array_sort(PG_FUNCTION_ARGS) if (ndim < 1 || dims[0] < 2) PG_RETURN_ARRAYTYPE_P(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) @@ -1860,8 +1872,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_UNDEFINED_FUNCTION), errmsg("could not identify ordering operator for type %s", @@ -1885,8 +1899,10 @@ array_sort(PG_FUNCTION_ARGS) (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)) + typentry = lookup_type_cache(array_type, + 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_UNDEFINED_FUNCTION), errmsg("could not identify ordering operator for type %s", @@ -1901,9 +1917,9 @@ array_sort(PG_FUNCTION_ARGS) } 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)) @@ -1935,3 +1951,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 54ebbbd135..c6593c63fb 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1747,6 +1747,12 @@ { 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', prosrc => 'array_sort_order'}, +{ oid => '8812', descr => 'sort array', + proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray', + proargtypes => 'anyarray bool bool', 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 9e7b444fd8..88c969ccbd 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 2b75a21f06..9cb73f3801 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.39.5