On 2/16/21 7:32 PM, Isaac Morland wrote:
> On Tue, 16 Feb 2021 at 12:54, Vik Fearing <v...@postgresfriends.org> 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
>From 21fb2060516a42b8b3edd1c9df3773ec8920d62b Mon Sep 17 00:00:00 2001
From: Vik Fearing <v...@postgresfriends.org>
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)') ...
</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
