From: Vik Fearing <vik@postgresfriends.org>
To: Isaac Morland <isaac.morland@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: TRIM_ARRAY
Date: Tue, 16 Feb 2021 23:38:30 +0100
Message-ID: <d6429c8e-1811-6456-b3f2-20ca5776076b@postgresfriends.org> (raw)
In-Reply-To: <CAMsGm5eseX1Sc1u=d5Prv_44CiMuhvtLnYFa2mFeic0oktqDWQ@mail.gmail.com>
References: <fc92ce17-9655-8ff1-c62a-4dc4c8ccd815@postgresfriends.org>
<CAMsGm5eseX1Sc1u=d5Prv_44CiMuhvtLnYFa2mFeic0oktqDWQ@mail.gmail.com>
On 2/16/21 7:32 PM, Isaac Morland wrote:
> On Tue, 16 Feb 2021 at 12:54, Vik Fearing <vik@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
Attachments:0001-implement-trim_array.v2.patch (text/x-patch, 4.0K) download
From 21fb2060516a42b8b3edd1c9df3773ec8920d62b Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@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.sgmlindex 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.datindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: vik@postgresfriends.org, isaac.morland@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: TRIM_ARRAY
In-Reply-To: <d6429c8e-1811-6456-b3f2-20ca5776076b@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox