public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] implement trim_array
28+ messages / 2 participants
[nested] [flat]
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* [PATCH] implement trim_array
@ 2021-02-16 17:38 Vik Fearing <[email protected]>
0 siblings, 0 replies; 28+ 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] 28+ messages in thread
* Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
@ 2024-12-31 03:52 Sami Imseih <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Sami Imseih @ 2024-12-31 03:52 UTC (permalink / raw)
To: Michail Nikolaev <[email protected]>; +Cc: Shayon Mukherjee <[email protected]>; David Rowley <[email protected]>; Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]>
> Should this not behave like if you drop (or create) an index
> during a prepared statement? I have not yet looked closely at
> this code to see what could be done.
>
> Regards,
I looked at this a bit more and ATExecEnableDisableIndex
needs some tweaks.
What should be getting invalidated in the heap relation
that the index is on and not the index relation as it is in
the current patch.
You can retrieve the heap relation oid
IndexGetRelation(indexOid, false) and the
CacheInvalidateRelcache should be on the heap relation.
The planner needs to only care about the heap relation
invalidation to re-plan across multiple executions of
a prepared statement.
There should be a test for this scenario as well.
Regards,
Sami
^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2024-12-31 03:52 UTC | newest]
Thread overview: 28+ 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]>
2024-12-31 03:52 Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) Sami Imseih <[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