Skip to content

Commit 6c12ae0

Browse files
tglsfdcjianhe-fun
tglsfdc
and
jianhe-fun
committed
Introduce a SQL-callable function array_sort(anyarray).
Create a function that will sort the elements of an array according to the element type's sort order. If the array has more than one dimension, the sub-arrays of the first dimension are sorted per normal array-comparison rules, leaving their contents alone. In support of this, add pg_type.typarray to the set of fields cached by the typcache. Author: Junwang Zhao <zhjwpku@gmail.com> Co-authored-by: Jian He <jian.universality@gmail.com> Reviewed-by: Aleksander Alekseev <aleksander@timescale.com> Discussion: https://postgr.es/m/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw@mail.gmail.com
1 parent 6da2ba1 commit 6c12ae0

File tree

11 files changed

+426
-1
lines changed

11 files changed

+426
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20741,6 +20741,42 @@ SELECT NULLIF(value, '(none)') ...
2074120741
</para></entry>
2074220742
</row>
2074320743

20744+
<row>
20745+
<entry role="func_table_entry"><para role="func_signature">
20746+
<indexterm>
20747+
<primary>array_sort</primary>
20748+
</indexterm>
20749+
<function>array_sort</function> (
20750+
<parameter>array</parameter> <type>anyarray</type>
20751+
<optional>, <parameter>descending</parameter> <type>boolean</type>
20752+
<optional>, <parameter>nulls_first</parameter> <type>boolean</type>
20753+
</optional></optional> )
20754+
<returnvalue>anyarray</returnvalue>
20755+
</para>
20756+
<para>
20757+
Sorts the first dimension of the array.
20758+
The sort order is determined by the default sort ordering of the
20759+
array's element type; however, if the element type is collatable,
20760+
the collation to use can be specified by adding
20761+
a <literal>COLLATE</literal> clause to
20762+
the <parameter>array</parameter> argument.
20763+
</para>
20764+
<para>
20765+
If <parameter>descending</parameter> is true then sort in
20766+
descending order, otherwise ascending order. If omitted, the
20767+
default is ascending order.
20768+
If <parameter>nulls_first</parameter> is true then nulls appear
20769+
before non-null values, otherwise nulls appear after non-null
20770+
values.
20771+
If omitted, <parameter>nulls_first</parameter> is taken to have
20772+
the same value as <parameter>descending</parameter>.
20773+
</para>
20774+
<para>
20775+
<literal>array_sort(ARRAY[[2,4],[2,1],[6,5]])</literal>
20776+
<returnvalue>{{2,1},{2,4},{6,5}}</returnvalue>
20777+
</para></entry>
20778+
</row>
20779+
2074420780
<row>
2074520781
<entry role="func_table_entry"><para role="func_signature">
2074620782
<indexterm id="function-array-to-string">

‎src/backend/utils/adt/array_userfuncs.c

Lines changed: 178 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,16 +12,19 @@
1212
*/
1313
#include "postgres.h"
1414

15+
#include "catalog/pg_operator_d.h"
1516
#include "catalog/pg_type.h"
1617
#include "common/int.h"
1718
#include "common/pg_prng.h"
1819
#include "libpq/pqformat.h"
20+
#include "miscadmin.h"
1921
#include "nodes/supportnodes.h"
2022
#include "port/pg_bitutils.h"
2123
#include "utils/array.h"
2224
#include "utils/builtins.h"
2325
#include "utils/datum.h"
2426
#include "utils/lsyscache.h"
27+
#include "utils/tuplesort.h"
2528
#include "utils/typcache.h"
2629

2730
/*
@@ -43,6 +46,18 @@ typedef struct DeserialIOData
4346
Oid typioparam;
4447
} DeserialIOData;
4548

49+
/*
50+
* ArraySortCachedInfo
51+
* Used for caching catalog data in array_sort
52+
*/
53+
typedef struct ArraySortCachedInfo
54+
{
55+
ArrayMetaState array_meta; /* metadata for array_create_iterator */
56+
Oid elem_lt_opr; /* "<" operator for element type */
57+
Oid elem_gt_opr; /* ">" operator for element type */
58+
Oid array_type; /* pg_type OID of array type */
59+
} ArraySortCachedInfo;
60+
4661
static Datum array_position_common(FunctionCallInfo fcinfo);
4762

4863

@@ -1858,3 +1873,166 @@ array_reverse(PG_FUNCTION_ARGS)
18581873

18591874
PG_RETURN_ARRAYTYPE_P(result);
18601875
}
1876+
1877+
/*
1878+
* array_sort
1879+
*
1880+
* Sorts the first dimension of the array.
1881+
*/
1882+
static ArrayType *
1883+
array_sort_internal(ArrayType *array, bool descending, bool nulls_first,
1884+
FunctionCallInfo fcinfo)
1885+
{
1886+
ArrayType *newarray;
1887+
Oid collation = PG_GET_COLLATION();
1888+
int ndim,
1889+
*dims,
1890+
*lbs;
1891+
ArraySortCachedInfo *cache_info;
1892+
Oid elmtyp;
1893+
Oid sort_typ;
1894+
Oid sort_opr;
1895+
Tuplesortstate *tuplesortstate;
1896+
ArrayIterator array_iterator;
1897+
Datum value;
1898+
bool isnull;
1899+
ArrayBuildStateAny *astate = NULL;
1900+
1901+
ndim = ARR_NDIM(array);
1902+
dims = ARR_DIMS(array);
1903+
lbs = ARR_LBOUND(array);
1904+
1905+
/* Quick exit if we don't need to sort */
1906+
if (ndim < 1 || dims[0] < 2)
1907+
return array;
1908+
1909+
/* Set up cache area if we didn't already */
1910+
cache_info = (ArraySortCachedInfo *) fcinfo->flinfo->fn_extra;
1911+
if (cache_info == NULL)
1912+
{
1913+
cache_info = (ArraySortCachedInfo *)
1914+
MemoryContextAllocZero(fcinfo->flinfo->fn_mcxt,
1915+
sizeof(ArraySortCachedInfo));
1916+
fcinfo->flinfo->fn_extra = cache_info;
1917+
}
1918+
1919+
/* Fetch and cache required data if we don't have it */
1920+
elmtyp = ARR_ELEMTYPE(array);
1921+
if (elmtyp != cache_info->array_meta.element_type)
1922+
{
1923+
TypeCacheEntry *typentry;
1924+
1925+
typentry = lookup_type_cache(elmtyp,
1926+
TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
1927+
cache_info->array_meta.element_type = elmtyp;
1928+
cache_info->array_meta.typlen = typentry->typlen;
1929+
cache_info->array_meta.typbyval = typentry->typbyval;
1930+
cache_info->array_meta.typalign = typentry->typalign;
1931+
cache_info->elem_lt_opr = typentry->lt_opr;
1932+
cache_info->elem_gt_opr = typentry->gt_opr;
1933+
cache_info->array_type = typentry->typarray;
1934+
}
1935+
1936+
/* Identify the sort operator to use */
1937+
if (ndim == 1)
1938+
{
1939+
/* Need to sort the element type */
1940+
sort_typ = elmtyp;
1941+
sort_opr = (descending ? cache_info->elem_gt_opr : cache_info->elem_lt_opr);
1942+
}
1943+
else
1944+
{
1945+
/* Otherwise we're sorting arrays */
1946+
sort_typ = cache_info->array_type;
1947+
if (!OidIsValid(sort_typ))
1948+
ereport(ERROR,
1949+
(errcode(ERRCODE_UNDEFINED_OBJECT),
1950+
errmsg("could not find array type for data type %s",
1951+
format_type_be(elmtyp))));
1952+
/* We know what operators to use for arrays */
1953+
sort_opr = (descending ? ARRAY_GT_OP : ARRAY_LT_OP);
1954+
}
1955+
1956+
/*
1957+
* Fail if we don't know how to sort. The error message is chosen to
1958+
* match what array_lt()/array_gt() will say in the multidimensional case.
1959+
*/
1960+
if (!OidIsValid(sort_opr))
1961+
ereport(ERROR,
1962+
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1963+
errmsg("could not identify a comparison function for type %s",
1964+
format_type_be(elmtyp)));
1965+
1966+
/* Put the things to be sorted (elements or sub-arrays) into a tuplesort */
1967+
tuplesortstate = tuplesort_begin_datum(sort_typ,
1968+
sort_opr,
1969+
collation,
1970+
nulls_first,
1971+
work_mem,
1972+
NULL,
1973+
TUPLESORT_NONE);
1974+
1975+
array_iterator = array_create_iterator(array, ndim - 1,
1976+
&cache_info->array_meta);
1977+
while (array_iterate(array_iterator, &value, &isnull))
1978+
{
1979+
tuplesort_putdatum(tuplesortstate, value, isnull);
1980+
}
1981+
array_free_iterator(array_iterator);
1982+
1983+
/* Do the sort */
1984+
tuplesort_performsort(tuplesortstate);
1985+
1986+
/* Extract results into a new array */
1987+
while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
1988+
{
1989+
astate = accumArrayResultAny(astate, value, isnull,
1990+
sort_typ, CurrentMemoryContext);
1991+
}
1992+
tuplesort_end(tuplesortstate);
1993+
1994+
newarray = DatumGetArrayTypeP(makeArrayResultAny(astate,
1995+
CurrentMemoryContext,
1996+
true));
1997+
1998+
/* Adjust lower bound to match the input */
1999+
ARR_LBOUND(newarray)[0] = lbs[0];
2000+
2001+
return newarray;
2002+
}
2003+
2004+
Datum
2005+
array_sort(PG_FUNCTION_ARGS)
2006+
{
2007+
ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
2008+
2009+
PG_RETURN_ARRAYTYPE_P(array_sort_internal(array,
2010+
false,
2011+
false,
2012+
fcinfo));
2013+
}
2014+
2015+
Datum
2016+
array_sort_order(PG_FUNCTION_ARGS)
2017+
{
2018+
ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
2019+
bool descending = PG_GETARG_BOOL(1);
2020+
2021+
PG_RETURN_ARRAYTYPE_P(array_sort_internal(array,
2022+
descending,
2023+
descending,
2024+
fcinfo));
2025+
}
2026+
2027+
Datum
2028+
array_sort_order_nulls_first(PG_FUNCTION_ARGS)
2029+
{
2030+
ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
2031+
bool descending = PG_GETARG_BOOL(1);
2032+
bool nulls_first = PG_GETARG_BOOL(2);
2033+
2034+
PG_RETURN_ARRAYTYPE_P(array_sort_internal(array,
2035+
descending,
2036+
nulls_first,
2037+
fcinfo));
2038+
}

‎src/backend/utils/cache/typcache.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -499,6 +499,7 @@ lookup_type_cache(Oid type_id, int flags)
499499
typentry->typrelid = typtup->typrelid;
500500
typentry->typsubscript = typtup->typsubscript;
501501
typentry->typelem = typtup->typelem;
502+
typentry->typarray = typtup->typarray;
502503
typentry->typcollation = typtup->typcollation;
503504
typentry->flags |= TCFLAGS_HAVE_PG_TYPE_DATA;
504505

@@ -544,6 +545,7 @@ lookup_type_cache(Oid type_id, int flags)
544545
typentry->typrelid = typtup->typrelid;
545546
typentry->typsubscript = typtup->typsubscript;
546547
typentry->typelem = typtup->typelem;
548+
typentry->typarray = typtup->typarray;
547549
typentry->typcollation = typtup->typcollation;
548550
typentry->flags |= TCFLAGS_HAVE_PG_TYPE_DATA;
549551

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202504011
60+
#define CATALOG_VERSION_NO 202504012
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1772,6 +1772,18 @@
17721772
{ oid => '8686', descr => 'reverse array',
17731773
proname => 'array_reverse', prorettype => 'anyarray',
17741774
proargtypes => 'anyarray', prosrc => 'array_reverse' },
1775+
{ oid => '8810', descr => 'sort array',
1776+
proname => 'array_sort', prorettype => 'anyarray', proargtypes => 'anyarray',
1777+
prosrc => 'array_sort' },
1778+
{ oid => '8811', descr => 'sort array',
1779+
proname => 'array_sort', prorettype => 'anyarray',
1780+
proargtypes => 'anyarray bool', proargnames => '{array,descending}',
1781+
prosrc => 'array_sort_order' },
1782+
{ oid => '8812', descr => 'sort array',
1783+
proname => 'array_sort', prorettype => 'anyarray',
1784+
proargtypes => 'anyarray bool bool',
1785+
proargnames => '{array,descending,nulls_first}',
1786+
prosrc => 'array_sort_order_nulls_first' },
17751787
{ oid => '3816', descr => 'array typanalyze',
17761788
proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
17771789
proargtypes => 'internal', prosrc => 'array_typanalyze' },

‎src/include/utils/typcache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@ typedef struct TypeCacheEntry
4444
Oid typrelid;
4545
Oid typsubscript;
4646
Oid typelem;
47+
Oid typarray;
4748
Oid typcollation;
4849

4950
/*

0 commit comments

Comments
 (0)