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

Commit0a687c8

Browse files
committed
Add trim_array() function.
This has been in the SQL spec since 2008. It's a pretty thinwrapper around the array slice functionality, but the specsays we should have it, so here it is.Vik Fearing, reviewed by Dian FayDiscussion:https://postgr.es/m/fc92ce17-9655-8ff1-c62a-4dc4c8ccd815@postgresfriends.org
1 parent3769e11 commit0a687c8

File tree

7 files changed

+100
-2
lines changed

7 files changed

+100
-2
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 0 deletions
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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -398,7 +398,7 @@ S301Enhanced UNNESTYES
398398
S401Distinct types based on array typesNO
399399
S402Distinct types based on distinct typesNO
400400
S403ARRAY_MAX_CARDINALITYNO
401-
S404TRIM_ARRAYNO
401+
S404TRIM_ARRAYYES
402402
T011Timestamp in Information SchemaNO
403403
T021BINARY and VARBINARY data typesNO
404404
T022Advanced support for BINARY and VARBINARY data typesNO

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6631,3 +6631,46 @@ width_bucket_array_variable(Datum operand,
66316631

66326632
returnleft;
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+
intn=PG_GETARG_INT32(1);
6644+
intarray_length=ARR_DIMS(v)[0];
6645+
int16elmlen;
6646+
boolelmbyval;
6647+
charelmalign;
6648+
intlower[MAXDIM];
6649+
intupper[MAXDIM];
6650+
boollowerProvided[MAXDIM];
6651+
boolupperProvided[MAXDIM];
6652+
Datumresult;
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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202103031
56+
#defineCATALOG_VERSION_NO202103032
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 0 deletions
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

Lines changed: 21 additions & 0 deletions
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

Lines changed: 13 additions & 0 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp