From 49d6c7d8dabad8c59ba21afe72ff32e79f822291 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Fri, 1 Nov 2024 10:32:19 +0900 Subject: [PATCH] Add SQL function array_reverse() This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle(). The implementation structure is inspired by array_shuffle(), with a subroutine called array_reverse_n() that may come in handy in the future, should more functions able to reverse portions of arrays be introduced. Bump catalog version. Author: Aleksander Alekseev Reviewed-by: Ashutosh Bapat, Tom Lane, Vladlen Popolitov Discussion: https://postgr.es/m/CAJ7c6TMpeO_ke+QGOaAx9xdJuxa7r=49-anMh3G5476e3CX1CA@mail.gmail.com --- doc/src/sgml/func.sgml | 17 ++++ src/backend/utils/adt/array_userfuncs.c | 112 ++++++++++++++++++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 3 + src/test/regress/expected/arrays.out | 31 +++++++ src/test/regress/sql/arrays.sql | 7 ++ 6 files changed, 171 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 05f630c6a6c..223d869f8c8 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20379,6 +20379,23 @@ SELECT NULLIF(value, '(none)') ... + + + + array_reverse + + array_reverse ( anyarray ) + anyarray + + + Reverses the first dimension of the array. + + + array_reverse(ARRAY[[1,2],[3,4],[5,6]]) + {{5,6},{3,4},{1,2}} + + + diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c index 6599be2ec5e..d053808f6ee 100644 --- a/src/backend/utils/adt/array_userfuncs.c +++ b/src/backend/utils/adt/array_userfuncs.c @@ -1685,3 +1685,115 @@ array_sample(PG_FUNCTION_ARGS) PG_RETURN_ARRAYTYPE_P(result); } + + +/* + * array_reverse_n + * Return a copy of array with reversed items. + * + * NOTE: it would be cleaner to look up the elmlen/elmbval/elmalign info + * from the system catalogs, given only the elmtyp. However, the caller is + * in a better position to cache this info across multiple calls. + */ +static ArrayType * +array_reverse_n(ArrayType *array, Oid elmtyp, TypeCacheEntry *typentry) +{ + ArrayType *result; + int ndim, + *dims, + *lbs, + nelm, + nitem, + rdims[MAXDIM], + rlbs[MAXDIM]; + int16 elmlen; + bool elmbyval; + char elmalign; + Datum *elms, + *ielms; + bool *nuls, + *inuls; + + ndim = ARR_NDIM(array); + dims = ARR_DIMS(array); + lbs = ARR_LBOUND(array); + + elmlen = typentry->typlen; + elmbyval = typentry->typbyval; + elmalign = typentry->typalign; + + deconstruct_array(array, elmtyp, elmlen, elmbyval, elmalign, + &elms, &nuls, &nelm); + + nitem = dims[0]; /* total number of items */ + nelm /= nitem; /* number of elements per item */ + + /* Reverse the array */ + ielms = elms; + inuls = nuls; + for (int i = 0; i < nitem / 2; i++) + { + int j = (nitem - i - 1) * nelm; + Datum *jelms = elms + j; + bool *jnuls = nuls + j; + + /* Swap i'th and j'th items; advance ielms/inuls to next item */ + for (int k = 0; k < nelm; k++) + { + Datum elm = *ielms; + bool nul = *inuls; + + *ielms++ = *jelms; + *inuls++ = *jnuls; + *jelms++ = elm; + *jnuls++ = nul; + } + } + + /* Set up dimensions of the result */ + memcpy(rdims, dims, ndim * sizeof(int)); + memcpy(rlbs, lbs, ndim * sizeof(int)); + rdims[0] = nitem; + + result = construct_md_array(elms, nuls, ndim, rdims, rlbs, + elmtyp, elmlen, elmbyval, elmalign); + + pfree(elms); + pfree(nuls); + + return result; +} + +/* + * array_reverse + * + * Returns an array with the same dimensions as the input array, with its + * first-dimension elements in reverse order. + */ +Datum +array_reverse(PG_FUNCTION_ARGS) +{ + ArrayType *array = PG_GETARG_ARRAYTYPE_P(0); + ArrayType *result; + Oid elmtyp; + TypeCacheEntry *typentry; + + /* + * There is no point in reversing empty arrays or arrays with less than + * two items. + */ + if (ARR_NDIM(array) < 1 || ARR_DIMS(array)[0] < 2) + PG_RETURN_ARRAYTYPE_P(array); + + elmtyp = ARR_ELEMTYPE(array); + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra; + if (typentry == NULL || typentry->type_id != elmtyp) + { + typentry = lookup_type_cache(elmtyp, 0); + fcinfo->flinfo->fn_extra = (void *) typentry; + } + + result = array_reverse_n(array, elmtyp, typentry); + + PG_RETURN_ARRAYTYPE_P(result); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 0fc51a2160d..e968e915e99 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202410311 +#define CATALOG_VERSION_NO 202411011 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1ec0d6f6b5f..a38e20f5d90 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1741,6 +1741,9 @@ { oid => '6216', descr => 'take samples from array', proname => 'array_sample', provolatile => 'v', prorettype => 'anyarray', proargtypes => 'anyarray int4', prosrc => 'array_sample' }, +{ oid => '8686', descr => 'reverse array', + proname => 'array_reverse', prorettype => 'anyarray', + proargtypes => 'anyarray', prosrc => 'array_reverse' }, { 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 a6d81fd5f92..0b61fb5bb78 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2703,3 +2703,34 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail ERROR: sample size must be between 0 and 6 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail ERROR: sample size must be between 0 and 6 +-- array_reverse +SELECT array_reverse('{}'::int[]); + array_reverse +--------------- + {} +(1 row) + +SELECT array_reverse('{1}'::int[]); + array_reverse +--------------- + {1} +(1 row) + +SELECT array_reverse('{1,2}'::int[]); + array_reverse +--------------- + {2,1} +(1 row) + +SELECT array_reverse('{1,2,3,NULL,4,5,6}'::int[]); + array_reverse +-------------------- + {6,5,4,NULL,3,2,1} +(1 row) + +SELECT array_reverse('{{1,2},{3,4},{5,6},{7,8}}'::int[]); + array_reverse +--------------------------- + {{7,8},{5,6},{3,4},{1,2}} +(1 row) + diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index 47058dfde50..691cff4a124 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -827,3 +827,10 @@ SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[] SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2)); SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail + +-- array_reverse +SELECT array_reverse('{}'::int[]); +SELECT array_reverse('{1}'::int[]); +SELECT array_reverse('{1,2}'::int[]); +SELECT array_reverse('{1,2,3,NULL,4,5,6}'::int[]); +SELECT array_reverse('{{1,2},{3,4},{5,6},{7,8}}'::int[]); \ No newline at end of file -- 2.30.2