Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6c12ae0

Browse files
tglsfdcjianhe-fun
andcommitted
Introduce a SQL-callable function array_sort(anyarray).
Create a function that will sort the elements of an arrayaccording to the element type's sort order. If the arrayhas more than one dimension, the sub-arrays of the firstdimension are sorted per normal array-comparison rules,leaving their contents alone.In support of this, add pg_type.typarray to the set of fieldscached 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 parent6da2ba1 commit6c12ae0

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
Oidtypioparam;
4447
}DeserialIOData;
4548

49+
/*
50+
* ArraySortCachedInfo
51+
*Used for caching catalog data in array_sort
52+
*/
53+
typedefstructArraySortCachedInfo
54+
{
55+
ArrayMetaStatearray_meta;/* metadata for array_create_iterator */
56+
Oidelem_lt_opr;/* "<" operator for element type */
57+
Oidelem_gt_opr;/* ">" operator for element type */
58+
Oidarray_type;/* pg_type OID of array type */
59+
}ArraySortCachedInfo;
60+
4661
staticDatumarray_position_common(FunctionCallInfofcinfo);
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+
staticArrayType*
1883+
array_sort_internal(ArrayType*array,booldescending,boolnulls_first,
1884+
FunctionCallInfofcinfo)
1885+
{
1886+
ArrayType*newarray;
1887+
Oidcollation=PG_GET_COLLATION();
1888+
intndim,
1889+
*dims,
1890+
*lbs;
1891+
ArraySortCachedInfo*cache_info;
1892+
Oidelmtyp;
1893+
Oidsort_typ;
1894+
Oidsort_opr;
1895+
Tuplesortstate*tuplesortstate;
1896+
ArrayIteratorarray_iterator;
1897+
Datumvalue;
1898+
boolisnull;
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+
returnarray;
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+
returnnewarray;
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+
booldescending=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+
booldescending=PG_GETARG_BOOL(1);
2032+
boolnulls_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-
#defineCATALOG_VERSION_NO202504011
60+
#defineCATALOG_VERSION_NO202504012
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
Oidtyprelid;
4545
Oidtypsubscript;
4646
Oidtypelem;
47+
Oidtyparray;
4748
Oidtypcollation;
4849

4950
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp