array_offset() and array_offsets()

These functions return the offset position or positions of a value in an
array.

Author: Pavel Stěhule
Reviewed by: Jim Nasby

2 files changed, 68 insertions, 0 deletions

diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index 9ea10682a56..092013b83b5 100644
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -600,6 +600,25 @@ SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
   index, as described in <xref linkend="indexes-types">.
  </para>
 
+ <para>
+  You can also search for specific values in an array using the <function>array_offset</>
+  and <function>array_offsets</> functions. The former returns the position of
+  the first occurrence of a value in an array; the latter returns an array with the
+  positions of all occurrences of the value in the array.  For example:
+
+<programlisting>
+SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
+ array_offset
+--------------
+ 2
+
+SELECT array_offsets(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
+ array_offsets 
+---------------
+ {1,4,8}
+</programlisting>
+ </para>
+
  <tip>
   <para>
    Arrays are not sets; searching for specific array elements
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c198beaf396..5843eaa9ffe 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11480,6 +11480,12 @@ SELECT NULLIF(value, '(none)') ...
     <primary>array_lower</primary>
   </indexterm>
   <indexterm>
+    <primary>array_offset</primary>
+  </indexterm>
+  <indexterm>
+    <primary>array_offsets</primary>
+  </indexterm>
+  <indexterm>
     <primary>array_prepend</primary>
   </indexterm>
   <indexterm>
@@ -11599,6 +11605,32 @@ SELECT NULLIF(value, '(none)') ...
        <row>
         <entry>
          <literal>
+          <function>array_offset</function>(<type>anyarray</type>, <type>anyelement</type> <optional>, <type>int</type></optional>)
+         </literal>
+        </entry>
+        <entry><type>int</type></entry>
+        <entry>returns the offset of the first occurrence of the second
+        argument in the array, starting at the element indicated by the third
+        argument or at the first element (array must be one-dimensional)</entry>
+        <entry><literal>array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon')</literal></entry>
+        <entry><literal>2</literal></entry>
+       </row>
+       <row>
+        <entry>
+         <literal>
+          <function>array_offsets</function>(<type>anyarray</type>, <type>anyelement</type>)
+         </literal>
+        </entry>
+        <entry><type>int[]</type></entry>
+        <entry>returns an array of offsets of all occurrences of the second
+        argument in the array given as first argument (array must be
+        one-dimensional)</entry>
+        <entry><literal>array_offsets(ARRAY['A','A','B','A'], 'A')</literal></entry>
+        <entry><literal>{1,2,4}</literal></entry>
+       </row>
+       <row>
+        <entry>
+         <literal>
           <function>array_prepend</function>(<type>anyelement</type>, <type>anyarray</type>)
          </literal>
         </entry>
@@ -11708,6 +11740,23 @@ NULL baz</literallayout>(3 rows)</entry>
     </table>
 
    <para>
+    In <function>array_offset</function> and <function>array_offsets</>,
+    each array element is compared to the searched value using
+    <literal>IS NOT DISTINCT FROM</literal> semantics.
+   </para>
+
+   <para>
+    In <function>array_offset</function>, <literal>NULL</literal> is returned
+    if the value is not found.
+   </para>
+
+   <para>
+    In <function>array_offsets</function>, <literal>NULL</literal> is returned
+    only if the array is <literal>NULL</literal>; if the value is not found in
+    the array, an empty array is returned instead.
+   </para>
+
+   <para>
     In <function>string_to_array</function>, if the delimiter parameter is
     NULL, each character in the input string will become a separate element in
     the resulting array.  If the delimiter is an empty string, then the entire