public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] implement trim_array 27+ messages / 1 participants [nested] [flat]
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 19 +++++++++++++++++++ src/include/catalog/pg_proc.dat | 4 ++++ src/test/regress/expected/arrays.out | 19 +++++++++++++++++++ src/test/regress/sql/arrays.sql | 16 ++++++++++++++++ 4 files changed, 58 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..c3e157622f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,25 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array to the elements 1 through <parameter>n</parameter>. Usually these are + the first <parameter>n</parameter> elements of the array, but may not be if the lower + bound is different from 1. + </para> + <para> + <literal>select trim_array(ARRAY[1,2,3,4,5,6], 3)</literal> + <returnvalue>{1,2,3}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..0aae4daf3b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,10 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', descr => 'trim an array down to n elements', + proname => 'trim_array', prolang => 'sql', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'select ($1)[1:$2]' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..a79ad36cb0 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,22 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------ + [-15:-10]={1,2,3,4,5,6} | {} + {1,2,3,4,5,6} | {1,2,3} + [10:15]={1,2,3,4,5,6} | {} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30}} +(4 rows) + +DROP TABLE trim_array_test; diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..6d34cc468e 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,19 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; -- 2.25.1 --------------5C31E1109E6FF9DF4672B1B7-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 17 +++++++++++++++++ src/include/catalog/pg_proc.dat | 4 ++++ src/test/regress/expected/arrays.out | 19 +++++++++++++++++++ src/test/regress/sql/arrays.sql | 16 ++++++++++++++++ 4 files changed, 56 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..58ab7860f4 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,23 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array to the first <parameter>n</parameter> elements. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 3)</literal> + <returnvalue>{1,2,3}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..76e2030865 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,10 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', descr => 'trim an array down to n elements', + proname => 'trim_array', prolang => 'sql', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'select ($1)[:array_lower($1, 1) + $2 - 1]' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..3fb1b8dcef 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,22 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------ + [-15:-10]={1,2,3,4,5,6} | {1,2,3} + {1,2,3,4,5,6} | {1,2,3} + [10:15]={1,2,3,4,5,6} | {1,2,3} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30}} +(4 rows) + +DROP TABLE trim_array_test; diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..6d34cc468e 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,19 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; -- 2.25.1 --------------FD71656EDFD96EADB800280D-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..aa5026af07 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index a24387c1e7..b9863f04e9 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------A2B3F8F0E95C0C23ACC1AE0D-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 27+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2021-02-16 17:38 UTC | newest] Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox