[PATCH] implement trim

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               | 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 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               | 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 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

* Re: In-placre persistance change of a relation
@ 2022-01-11 09:33 Jakub Wartak <[email protected]>
  0 siblings, 0 replies; 28+ messages in thread

From: Jakub Wartak @ 2022-01-11 09:33 UTC (permalink / raw)
  To: [email protected]; +Cc: Kyotaro Horiguchi <[email protected]>

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            not tested

I've retested v15 of the patch with everything that came to my mind. The patch passes all my tests (well, there's this just windows / cfbot issue). Patch looks good to me. I haven't looked in-depth at the code, so patch might still need review.

FYI, about potential usage of this patch: the most advanced test that I did was continually bouncing wal_level - it works. So chain of :
1. wal_level=replica->minimal
2. alter table set unlogged and load a lot of data, set logged
3. wal_level=minimal->replica
4. barman incremental backup # rsync(1) just backups changed files in steps 2 and 3 (not whole DB)
5. some other (logged) work
The idea is that when performing mass-alterations to the DB (think nightly ETL/ELT on TB-sized DBs), one could skip backing up most of DB and then just quickly backup only the changed files - during the maintenance window - e.g. thanks to local-rsync barman mode. This is the output of barman show-backups after loading data to unlogged table each such cycle:
mydb 20220110T100236 - Mon Jan 10 10:05:14 2022 - Size: 144.1 GiB - WAL Size: 16.0 KiB
mydb 20220110T094905 - Mon Jan 10 09:50:12 2022 - Size: 128.5 GiB - WAL Size: 80.2 KiB
mydb 20220110T094016 - Mon Jan 10 09:40:17 2022 - Size: 109.1 GiB - WAL Size: 496.3 KiB
And dedupe ratio of the last one: Backup size: 144.1 GiB. Actual size on disk: 36.1 GiB (-74.96% deduplication ratio).  

The only thing I've found out that bouncing wal_level also forces max_wal_senders=X -> 0 -> X which in turn requires dropping replication slot for pg_receievewal (e.g. barman receive-wal --create-slot/--drop-slot/--reset). I have tested the restore using barman recover afterwards to backup 20220110T094905  and indeed it worked OK using this patch too.

The new status of this patch is: Needs review

^ permalink  raw  reply  [nested|flat] 28+ messages in thread

end of thread, other threads:[~2022-01-11 09:33 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]>
2022-01-11 09:33 Re: In-placre persistance change of a relation Jakub Wartak <[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