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 1lC8zR-0002yV-Sy for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Feb 2021 22:38:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lC8zP-0004UO-JL for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Feb 2021 22:38:39 +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 1lC8zP-0004UH-CB for pgsql-hackers@lists.postgresql.org; Tue, 16 Feb 2021 22:38:39 +0000 Received: from relay11.mail.gandi.net ([217.70.178.231]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lC8zL-0004lc-96 for pgsql-hackers@lists.postgresql.org; Tue, 16 Feb 2021 22:38:38 +0000 Received: from [172.16.255.146] (14.149.88.79.rev.sfr.net [79.88.149.14]) (Authenticated sender: vik@postgresfriends.org) by relay11.mail.gandi.net (Postfix) with ESMTPSA id 34E1D100005; Tue, 16 Feb 2021 22:38:30 +0000 (UTC) Subject: Re: TRIM_ARRAY To: Isaac Morland Cc: PostgreSQL Hackers References: From: Vik Fearing Message-ID: Date: Tue, 16 Feb 2021 23:38:30 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------FD71656EDFD96EADB800280D" 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. --------------FD71656EDFD96EADB800280D Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit On 2/16/21 7:32 PM, Isaac Morland wrote: > On Tue, 16 Feb 2021 at 12:54, Vik Fearing wrote: > >> 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. >> > > I don't recall ever seeing non-default lower bounds, so I actually think > it's OK to just rule out that scenario, but why not something like this: > > ($1)[:array_lower ($1, 1) + $2 - 1] I'm kind of embarrassed that I didn't think about doing that; it is a much better solution. You lose the non-standard bounds but I don't think there is any way besides C to keep the lower bound regardless of how you trim it. V2 attached. -- Vik Fearing --------------FD71656EDFD96EADB800280D Content-Type: text/x-patch; charset=UTF-8; name="0001-implement-trim_array.v2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-implement-trim_array.v2.patch" From 21fb2060516a42b8b3edd1c9df3773ec8920d62b 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 | 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)') ... + + + + trim_array + + trim_array ( array anyarray, n integer ) + anyarray + + + Trims an array to the first n elements. + + + 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..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--