From: Vik Fearing <vik@postgresfriends.org>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: TRIM_ARRAY
Date: Tue, 16 Feb 2021 18:54:11 +0100
Message-ID: <fc92ce17-9655-8ff1-c62a-4dc4c8ccd815@postgresfriends.org> (raw)
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
Attachments:0001-implement-trim_array.patch (text/x-patch, 4.2K) download
From 6429316ab6060a16889b7c188ca577e17a5c7e4c 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 | 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.sgmlindex 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.datindex 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.outindex 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.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, pgsql-hackers@lists.postgresql.org
Subject: Re: TRIM_ARRAY
In-Reply-To: <fc92ce17-9655-8ff1-c62a-4dc4c8ccd815@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