Skip to content

Commit 0a687c8

Browse files
tglsfdctglsfdc
committed
Add trim_array() function.
This has been in the SQL spec since 2008. It's a pretty thin wrapper around the array slice functionality, but the spec says we should have it, so here it is. Vik Fearing, reviewed by Dian Fay Discussion: https://postgr.es/m/fc92ce17-9655-8ff1-c62a-4dc4c8ccd815@postgresfriends.org
1 parent 3769e11 commit 0a687c8

File tree

7 files changed

+100
-2
lines changed

7 files changed

+100
-2
lines changed

‎doc/src/sgml/func.sgml

+18
Original file line numberDiff line numberDiff line change
@@ -17930,6 +17930,24 @@ SELECT NULLIF(value, '(none)') ...
1793017930
</para></entry>
1793117931
</row>
1793217932

17933+
<row>
17934+
<entry role="func_table_entry"><para role="func_signature">
17935+
<indexterm>
17936+
<primary>trim_array</primary>
17937+
</indexterm>
17938+
<function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> )
17939+
<returnvalue>anyarray</returnvalue>
17940+
</para>
17941+
<para>
17942+
Trims an array by removing the last <parameter>n</parameter> elements.
17943+
If the array is multidimensional, only the first dimension is trimmed.
17944+
</para>
17945+
<para>
17946+
<literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal>
17947+
<returnvalue>{1,2,3,4}</returnvalue>
17948+
</para></entry>
17949+
</row>
17950+
1793317951
<row>
1793417952
<entry role="func_table_entry"><para role="func_signature">
1793517953
<indexterm>

‎src/backend/catalog/sql_features.txt

+1-1
Original file line numberDiff line numberDiff line change
@@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES
398398
S401 Distinct types based on array types NO
399399
S402 Distinct types based on distinct types NO
400400
S403 ARRAY_MAX_CARDINALITY NO
401-
S404 TRIM_ARRAY NO
401+
S404 TRIM_ARRAY YES
402402
T011 Timestamp in Information Schema NO
403403
T021 BINARY and VARBINARY data types NO
404404
T022 Advanced support for BINARY and VARBINARY data types NO

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

+43
Original file line numberDiff line numberDiff line change
@@ -6631,3 +6631,46 @@ width_bucket_array_variable(Datum operand,
66316631

66326632
return left;
66336633
}
6634+
6635+
/*
6636+
* Trim the last N elements from an array by building an appropriate slice.
6637+
* Only the first dimension is trimmed.
6638+
*/
6639+
Datum
6640+
trim_array(PG_FUNCTION_ARGS)
6641+
{
6642+
ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
6643+
int n = PG_GETARG_INT32(1);
6644+
int array_length = ARR_DIMS(v)[0];
6645+
int16 elmlen;
6646+
bool elmbyval;
6647+
char elmalign;
6648+
int lower[MAXDIM];
6649+
int upper[MAXDIM];
6650+
bool lowerProvided[MAXDIM];
6651+
bool upperProvided[MAXDIM];
6652+
Datum result;
6653+
6654+
/* Per spec, throw an error if out of bounds */
6655+
if (n < 0 || n > array_length)
6656+
ereport(ERROR,
6657+
(errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
6658+
errmsg("number of elements to trim must be between 0 and %d",
6659+
array_length)));
6660+
6661+
/* Set all the bounds as unprovided except the first upper bound */
6662+
memset(lowerProvided, false, sizeof(lowerProvided));
6663+
memset(upperProvided, false, sizeof(upperProvided));
6664+
upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1;
6665+
upperProvided[0] = true;
6666+
6667+
/* Fetch the needed information about the element type */
6668+
get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign);
6669+
6670+
/* Get the slice */
6671+
result = array_get_slice(PointerGetDatum(v), 1,
6672+
upper, lower, upperProvided, lowerProvided,
6673+
-1, elmlen, elmbyval, elmalign);
6674+
6675+
PG_RETURN_DATUM(result);
6676+
}

‎src/include/catalog/catversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202103031
56+
#define CATALOG_VERSION_NO 202103032
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

+3
Original file line numberDiff line numberDiff line change
@@ -1663,6 +1663,9 @@
16631663
proname => 'width_bucket', prorettype => 'int4',
16641664
proargtypes => 'anycompatible anycompatiblearray',
16651665
prosrc => 'width_bucket_array' },
1666+
{ oid => '8819', descr => 'remove last N elements of array',
1667+
proname => 'trim_array', prorettype => 'anyarray',
1668+
proargtypes => 'anyarray int4', prosrc => 'trim_array' },
16661669
{ oid => '3816', descr => 'array typanalyze',
16671670
proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
16681671
proargtypes => 'internal', prosrc => 'array_typanalyze' },

‎src/test/regress/expected/arrays.out

+21
Original file line numberDiff line numberDiff line change
@@ -2399,3 +2399,24 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]);
23992399
ERROR: thresholds array must not contain NULLs
24002400
SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
24012401
ERROR: thresholds must be one-dimensional array
2402+
-- trim_array
2403+
SELECT arr, trim_array(arr, 2)
2404+
FROM
2405+
(VALUES ('{1,2,3,4,5,6}'::bigint[]),
2406+
('{1,2}'),
2407+
('[10:16]={1,2,3,4,5,6,7}'),
2408+
('[-15:-10]={1,2,3,4,5,6}'),
2409+
('{{1,10},{2,20},{3,30},{4,40}}')) v(arr);
2410+
arr | trim_array
2411+
-------------------------------+-----------------
2412+
{1,2,3,4,5,6} | {1,2,3,4}
2413+
{1,2} | {}
2414+
[10:16]={1,2,3,4,5,6,7} | {1,2,3,4,5}
2415+
[-15:-10]={1,2,3,4,5,6} | {1,2,3,4}
2416+
{{1,10},{2,20},{3,30},{4,40}} | {{1,10},{2,20}}
2417+
(5 rows)
2418+
2419+
SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail
2420+
ERROR: number of elements to trim must be between 0 and 3
2421+
SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail
2422+
ERROR: number of elements to trim must be between 0 and 3

‎src/test/regress/sql/arrays.sql

+13
Original file line numberDiff line numberDiff line change
@@ -722,3 +722,16 @@ SELECT width_bucket(5, '{}');
722722
SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]);
723723
SELECT width_bucket(5, ARRAY[3, 4, NULL]);
724724
SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
725+
726+
-- trim_array
727+
728+
SELECT arr, trim_array(arr, 2)
729+
FROM
730+
(VALUES ('{1,2,3,4,5,6}'::bigint[]),
731+
('{1,2}'),
732+
('[10:16]={1,2,3,4,5,6,7}'),
733+
('[-15:-10]={1,2,3,4,5,6}'),
734+
('{{1,10},{2,20},{3,30},{4,40}}')) v(arr);
735+
736+
SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail
737+
SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail

0 commit comments

Comments
 (0)