($INBOX_DIR/description missing)  

help / color / mirror / Atom feed

[PATCH] implement trim_array
29+ messages / 3 participants
[nested] [flat]

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* [PATCH] implement trim_array
@ 2021-02-16 17:38  Vik Fearing <[email protected]>
  0 siblings, 0 replies; 29+ 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] 29+ messages in thread

* Re: Proposal: In-flight explain logging
@ 2023-12-02 20:56  Rafael Thofehrn Castro <[email protected]>
  0 siblings, 1 reply; 29+ messages in thread

From: Rafael Thofehrn Castro @ 2023-12-02 20:56 UTC (permalink / raw)
  To: Maciek Sakrejda <[email protected]>; +Cc: pgsql-hackers

Hello Maciek,

Thanks for pointing that out. They are indeed super similar. Before I wrote
the patch I searched for
"explain" related ones. I guess I should have performed a better search.

Comparing the patches, there is one main difference: the existing patch
prints only the plan without
any instrumentation details of the current execution state at that
particular time. That is precisely what
I am looking for with this new feature.

I guess the way to proceed here would be to use the already existing patch
as a lot of work was done
there already.


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

* Re: Proposal: In-flight explain logging
@ 2023-12-04 16:18  Ashutosh Bapat <[email protected]>
  parent: Rafael Thofehrn Castro <[email protected]>
  0 siblings, 0 replies; 29+ messages in thread

From: Ashutosh Bapat @ 2023-12-04 16:18 UTC (permalink / raw)
  To: Rafael Thofehrn Castro <[email protected]>; +Cc: Maciek Sakrejda <[email protected]>; pgsql-hackers

Hi Rafael,

On Sun, Dec 3, 2023 at 2:27 AM Rafael Thofehrn Castro
<[email protected]> wrote:
>
> Hello Maciek,
>
> Thanks for pointing that out. They are indeed super similar. Before I wrote the patch I searched for
> "explain" related ones. I guess I should have performed a better search.
>
> Comparing the patches, there is one main difference: the existing patch prints only the plan without
> any instrumentation details of the current execution state at that particular time. That is precisely what
> I am looking for with this new feature.
>
> I guess the way to proceed here would be to use the already existing patch as a lot of work was done
> there already.

It might help if you add an incremental patch for reporting
instrumentation details on top of already existing patches on that
thread.

-- 
Best Wishes,
Ashutosh Bapat






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

end of thread, other threads:[~2023-12-04 16:18 UTC | newest]

Thread overview: 29+ 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]>
2023-12-02 20:56 Re: Proposal: In-flight explain logging Rafael Thofehrn Castro <[email protected]>
2023-12-04 16:18 ` Re: Proposal: In-flight explain logging Ashutosh Bapat <[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