Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lC4YI-0000cn-5L for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Feb 2021 17:54:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lC4YH-0008Av-3X for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Feb 2021 17:54:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lC4YG-0008Ao-Sh for pgsql-hackers@lists.postgresql.org; Tue, 16 Feb 2021 17:54:20 +0000 Received: from relay1-d.mail.gandi.net ([217.70.183.193]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lC4YD-0002ZQ-A5 for pgsql-hackers@lists.postgresql.org; Tue, 16 Feb 2021 17:54:19 +0000 X-Originating-IP: 79.88.149.14 Received: from [172.16.255.146] (14.149.88.79.rev.sfr.net [79.88.149.14]) (Authenticated sender: vik@postgresfriends.org) by relay1-d.mail.gandi.net (Postfix) with ESMTPSA id F0EC9240003 for ; Tue, 16 Feb 2021 17:54:12 +0000 (UTC) To: PostgreSQL Hackers From: Vik Fearing Subject: TRIM_ARRAY Message-ID: Date: Tue, 16 Feb 2021 18:54:11 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------5C31E1109E6FF9DF4672B1B7" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------5C31E1109E6FF9DF4672B1B7 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit The SQL standard defines a function called TRIM_ARRAY that surprisingly has syntax that looks like a function! So I implemented it using a thin wrapper around our array slice syntax. It is literally just ($1)[1:$2]. An interesting case that I decided to handle by explaining it in the docs is that this won't give you the first n elements if your lower bound is not 1. My justification for this is 1) non-standard lower bounds are so rare in the wild that 2) people using them can just not use this function. The alternative is to go through the unnest dance (or write it in C) which defeats inlining. Patch attached. -- Vik Fearing --------------5C31E1109E6FF9DF4672B1B7 Content-Type: text/x-patch; charset=UTF-8; name="0001-implement-trim_array.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-implement-trim_array.patch" From 6429316ab6060a16889b7c188ca577e17a5c7e4c Mon Sep 17 00:00:00 2001 From: Vik Fearing Date: Tue, 16 Feb 2021 18:38:24 +0100 Subject: [PATCH] implement trim_array --- 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)') ... + + + + trim_array + + trim_array ( array anyarray, n integer ) + anyarray + + + Trims an array to the elements 1 through n. Usually these are + the first n elements of the array, but may not be if the lower + bound is different from 1. + + + select trim_array(ARRAY[1,2,3,4,5,6], 3) + {1,2,3} + + + 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--