From 13dbc7a824b3f905904cab51840d37f31a07a9ef Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 18 Mar 2015 16:01:34 -0300
Subject: array_offset() and array_offsets()
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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

Author: Pavel Stěhule
Reviewed by: Jim Nasby
---
 doc/src/sgml/array.sgml | 19 +++++++++++++++++++
 doc/src/sgml/func.sgml  | 49 +++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 68 insertions(+)

(limited to 'doc/src')

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
@@ -11479,6 +11479,12 @@ SELECT NULLIF(value, '(none)') ...
   <indexterm>
     <primary>array_lower</primary>
   </indexterm>
+  <indexterm>
+    <primary>array_offset</primary>
+  </indexterm>
+  <indexterm>
+    <primary>array_offsets</primary>
+  </indexterm>
   <indexterm>
     <primary>array_prepend</primary>
   </indexterm>
@@ -11596,6 +11602,32 @@ SELECT NULLIF(value, '(none)') ...
         <entry><literal>array_lower('[0:2]={1,2,3}'::int[], 1)</literal></entry>
         <entry><literal>0</literal></entry>
        </row>
+       <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>
@@ -11707,6 +11739,23 @@ NULL baz</literallayout>(3 rows)</entry>
      </tgroup>
     </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
-- 
cgit v1.2.3