Re: array support patch phase 1 patch - Mailing list pgsql-patches

Tom Lane wrote:
> I've applied much but not all of this.  Some comments:

Here (finally!) is a doc patch to go with the previously applied array
support changes. If there are no objections, please apply.

Thanks,

Joe
Index: doc/src/sgml/array.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/array.sgml,v
retrieving revision 1.25
diff -c -r1.25 array.sgml
*** doc/src/sgml/array.sgml    13 Mar 2003 01:30:26 -0000    1.25
--- doc/src/sgml/array.sgml    4 May 2003 05:14:04 -0000
***************
*** 9,15 ****

   <para>
    <productname>PostgreSQL</productname> allows columns of a table to be
!   defined as variable-length multidimensional arrays. Arrays of any
    built-in type or user-defined type can be created.
   </para>

--- 9,15 ----

   <para>
    <productname>PostgreSQL</productname> allows columns of a table to be
!   defined as variable-length multi-dimensional arrays. Arrays of any
    built-in type or user-defined type can be created.
   </para>

***************
*** 60,73 ****
  </programlisting>
   </para>

   <note>
    <para>
!    A limitation of the present array implementation is that individual
!    elements of an array cannot be SQL null values.  The entire array can be set
!    to null, but you can't have an array with some elements null and some
!    not.  Fixing this is on the to-do list.
    </para>
   </note>
   </sect2>

   <sect2>
--- 60,133 ----
  </programlisting>
   </para>

+  <para>
+   A limitation of the present array implementation is that individual
+   elements of an array cannot be SQL null values.  The entire array can be set
+   to null, but you can't have an array with some elements null and some
+   not.
+  </para>
+  <para>
+   This can lead to surprising results. For example, the result of the
+   previous two inserts looks like this:
+ <programlisting>
+ SELECT * FROM sal_emp;
+  name  |      pay_by_quarter       |      schedule
+ -------+---------------------------+--------------------
+  Bill  | {10000,10000,10000,10000} | {{meeting},{""}}
+  Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
+ (2 rows)
+ </programlisting>
+   Because the <literal>[2][2]</literal> element of
+   <structfield>schedule</structfield> is missing in each of the
+   <command>INSERT</command> statements, the <literal>[1][2]</literal>
+   element is discarded.
+  </para>
+
+  <note>
+   <para>
+    Fixing this is on the to-do list.
+   </para>
+  </note>
+
+  <para>
+   The <command>ARRAY</command> expression syntax may also be used:
+ <programlisting>
+ INSERT INTO sal_emp
+     VALUES ('Bill',
+     ARRAY[10000, 10000, 10000, 10000],
+     ARRAY[['meeting', 'lunch'], ['','']]);
+
+ INSERT INTO sal_emp
+     VALUES ('Carol',
+     ARRAY[20000, 25000, 25000, 25000],
+     ARRAY[['talk', 'consult'], ['meeting', '']]);
+ SELECT * FROM sal_emp;
+  name  |      pay_by_quarter       |           schedule
+ -------+---------------------------+-------------------------------
+  Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
+  Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
+ (2 rows)
+ </programlisting>
+   Note that with this syntax, multi-dimesion arrays must have matching
+   extents for each dimension. This eliminates the missing-array-elements
+   problem above. For example:
+ <programlisting>
+ INSERT INTO sal_emp
+     VALUES ('Carol',
+     ARRAY[20000, 25000, 25000, 25000],
+     ARRAY[['talk', 'consult'], ['meeting']]);
+ ERROR:  Multiple dimension arrays must have array expressions with matching dimensions
+ </programlisting>
+   Also notice that string literals are single quoted instead of double quoted.
+  </para>
+
   <note>
    <para>
!    The examples in the rest of this section are based on the
!    <command>ARRAY</command> expression syntax <command>INSERT</command>s.
    </para>
   </note>
+
   </sect2>

   <sect2>
***************
*** 132,142 ****
  </programlisting>

    with the same result.  An array subscripting operation is always taken to
!   represent an array slice if any of the subscripts are written in the
!   form
    <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
    A lower bound of 1 is assumed for any subscript where only one value
!   is specified.
   </para>

   <para>
--- 192,221 ----
  </programlisting>

    with the same result.  An array subscripting operation is always taken to
!   represent an array slice if any of the subscripts are written in the form
    <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
    A lower bound of 1 is assumed for any subscript where only one value
!   is specified; another example follows:
! <programlisting>
! SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
!          schedule
! ---------------------------
!  {{meeting,lunch},{"",""}}
! (1 row)
! </programlisting>
!  </para>
!
!  <para>
!   Additionally, we can also access a single arbitrary array element of
!   a one-dimensional array with the <function>array_subscript</function>
!   function:
! <programlisting>
! SELECT array_subscript(pay_by_quarter, 2) FROM sal_emp WHERE name = 'Bill';
!  array_subscript
! -----------------
!            10000
! (1 row)
! </programlisting>
   </para>

   <para>
***************
*** 147,153 ****
      WHERE name = 'Carol';
  </programlisting>

!   or updated at a single element:

  <programlisting>
  UPDATE sal_emp SET pay_by_quarter[4] = 15000
--- 226,248 ----
      WHERE name = 'Carol';
  </programlisting>

!   or using the <command>ARRAY</command> expression syntax:
!
! <programlisting>
! UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
!     WHERE name = 'Carol';
! </programlisting>
!
!   <note>
!    <para>
!     Anywhere you can use the <quote>curly braces</quote> array syntax,
!     you can also use the <command>ARRAY</command> expression syntax. The
!     remainder of this section will illustrate only one or the other, but
!     not both.
!    </para>
!   </note>
!
!   An array may also be updated at a single element:

  <programlisting>
  UPDATE sal_emp SET pay_by_quarter[4] = 15000
***************
*** 160,165 ****
--- 255,268 ----
  UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
      WHERE name = 'Carol';
  </programlisting>
+
+   A one-dimensional array may also be updated with the
+   <function>array_assign</function> function:
+
+ <programlisting>
+ UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000)
+     WHERE name = 'Bill';
+ </programListing>
   </para>

   <para>
***************
*** 169,175 ****
    value currently has 4 elements, it will have five elements after an
    update that assigns to <literal>array[5]</>.  Currently, enlargement in
    this fashion is only allowed for one-dimensional arrays, not
!   multidimensional arrays.
   </para>

   <para>
--- 272,278 ----
    value currently has 4 elements, it will have five elements after an
    update that assigns to <literal>array[5]</>.  Currently, enlargement in
    this fashion is only allowed for one-dimensional arrays, not
!   multi-dimensional arrays.
   </para>

   <para>
***************
*** 179,184 ****
--- 282,367 ----
   </para>

   <para>
+   An array can also be enlarged by using the functions
+   <function>array_prepend</function>, <function>array_append</function>,
+   or <function>array_cat</function>. The first two only support one-dimensional
+   arrays, but <function>array_cat</function> supports multi-dimensional arrays.
+   Some examples:
+
+ <programlisting>
+ SELECT array_prepend(1, ARRAY[2,3]);
+  array_prepend
+ ---------------
+  {1,2,3}
+ (1 row)
+
+ SELECT array_append(ARRAY[1,2], 3);
+  array_append
+ --------------
+  {1,2,3}
+ (1 row)
+
+ SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
+    array_cat
+ ---------------
+  {{1,2},{3,4}}
+ (1 row)
+
+ SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
+       array_cat
+ ---------------------
+  {{1,2},{3,4},{5,6}}
+ (1 row)
+
+ SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
+       array_cat
+ ---------------------
+  {{5,6},{1,2},{3,4}}
+ </programlisting>
+
+   <function>array_prepend</function> and <function>array_append</function>
+   work with a one-dimensional array and a single element to be pushed on
+   to the beginning or end of the array, respectively. The array is extended
+   in the direction of the push. Hence, by pushing onto the beginning of an
+   array with a one-based subscript, a zero-based subscript array is created:
+
+ <programlisting>
+ SELECT array_dims(t.f) FROM (SELECT array_prepend(1, ARRAY[2,3]) AS f) AS t;
+  array_dims
+ ------------
+  [0:2]
+ (1 row)
+ </programlisting>
+
+   <function>array_cat</function> works with either two
+   <replaceable>n</>-dimension arrays, or an <replaceable>n</>-dimension
+   and an <replaceable>n+1</> dimension array. In the former case, the two
+   <replaceable>n</>-dimension arrays become outer elements of an
+   <replaceable>n+1</> dimension array. In the latter, the
+   <replaceable>n</>-dimension array is added as either the first or last
+   outer element of the <replaceable>n+1</> dimension array.
+  </para>
+
+  <para>
+   A final method of enlarging arrays is through the concatenation operator,
+   <command>||</command>, which works exactly as <function>array_cat</function>
+   does.
+ <programlisting>
+ SELECT ARRAY[1,2] || ARRAY[3,4];
+    ?column?
+ ---------------
+  {{1,2},{3,4}}
+ (1 row)
+
+ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
+       ?column?
+ ---------------------
+  {{5,6},{1,2},{3,4}}
+ (1 row)
+ </programlisting>
+  </para>
+
+  <para>
    The syntax for <command>CREATE TABLE</command> allows fixed-length
    arrays to be defined:

***************
*** 194,199 ****
--- 377,392 ----
   </para>

   <para>
+   An alternative syntax for one-dimensional arrays may be used.
+   <structfield>pay_by_quarter</structfield> could have been defined as:
+ <programlisting>
+     pay_by_quarter  integer ARRAY[4],
+ </programlisting>
+   This syntax may <emphasis>only</emphasis> be used with the integer
+   constant to denote the array size.
+  </para>
+
+  <para>
    Actually, the current implementation does not enforce the declared
    number of dimensions either.  Arrays of a particular element type are
    all considered to be of the same type, regardless of size or number
***************
*** 292,298 ****
     for the array's element type.  (Among the standard data types provided
     in the <productname>PostgreSQL</productname> distribution, type
     <literal>box</> uses a semicolon (<literal>;</>) but all the others
!    use comma.)  In a multidimensional array, each dimension (row, plane,
     cube, etc.) gets its own level of curly braces, and delimiters
     must be written between adjacent curly-braced entities of the same level.
     You may write whitespace before a left brace, after a right
--- 485,491 ----
     for the array's element type.  (Among the standard data types provided
     in the <productname>PostgreSQL</productname> distribution, type
     <literal>box</> uses a semicolon (<literal>;</>) but all the others
!    use comma.)  In a multi-dimensional array, each dimension (row, plane,
     cube, etc.) gets its own level of curly braces, and delimiters
     must be written between adjacent curly-braced entities of the same level.
     You may write whitespace before a left brace, after a right
***************
*** 300,305 ****
--- 493,564 ----
     is not ignored, however: after skipping leading whitespace, everything
     up to the next right brace or delimiter is taken as the item value.
    </para>
+
+   <para>
+    As illustrated earlier in this chapter, arrays may also be represented
+    using the <command>ARRAY</command> expression syntax. This representation
+    of an array value consists of items that are interpreted according to the
+    I/O conversion rules for the array's element type, plus decoration that
+    indicates the array structure. The decoration consists of the keyword
+    <command>ARRAY</command> and square brackets (<literal>[</> and
+    <literal>]</>) around the array values, plus delimiter characters between
+    adjacent items. The delimiter character is always a comma (<literal>,</>).
+    When representing multi-dimensional arrays, the keyword
+    <command>ARRAY</command> is only necessary for the outer level. For example,
+    <literal>'{{"hello world", "happy birthday"}}'</literal> could be written as:
+ <programlisting>
+ SELECT ARRAY[['hello world', 'happy birthday']];
+                array
+ ------------------------------------
+  {{"hello world","happy birthday"}}
+ (1 row)
+ </programlisting>
+   or it also could be written as:
+ <programlisting>
+ SELECT ARRAY[ARRAY['hello world', 'happy birthday']];
+                array
+ ------------------------------------
+  {{"hello world","happy birthday"}}
+ (1 row)
+ </programlisting>
+   </para>
+
+   <para>
+    A final method to represent an array, is through an
+    <command>ARRAY</command> sub-select expression. For example:
+ <programlisting>
+ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
+                           ?column?
+ -------------------------------------------------------------
+  {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
+ (1 row)
+ </programlisting>
+   The sub-select may <emphasis>only</emphasis> return a single column. The
+   resulting one-dimensional array will have an element for each row in the
+   sub-select result, with an element type matching that of the sub-select's
+   target column.
+   </para>
+
+   <para>
+    Arrays may be cast from one type to another in similar fashion to other
+    data types:
+
+ <programlisting>
+ SELECT ARRAY[1,2,3]::oid[];
+   array
+ ---------
+  {1,2,3}
+ (1 row)
+
+ SELECT CAST(ARRAY[1,2,3] AS float8[]);
+   array
+ ---------
+  {1,2,3}
+ (1 row)
+ </programlisting>
+
+   </para>
+
   </sect2>

   <sect2>
***************
*** 316,321 ****
--- 575,588 ----
     Alternatively, you can use backslash-escaping to protect all data characters
     that would otherwise be taken as array syntax or ignorable white space.
    </para>
+
+  <note>
+   <para>
+    The discussion in the preceding paragraph with respect to double quoting does
+    not pertain to the <command>ARRAY</command> expression syntax. In that case,
+    each element is quoted exactly as any other literal value of the element type.
+   </para>
+  </note>

    <para>
     The array output routine will put double quotes around element values
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.153
diff -c -r1.153 func.sgml
*** doc/src/sgml/func.sgml    1 May 2003 00:57:05 -0000    1.153
--- doc/src/sgml/func.sgml    5 May 2003 00:42:26 -0000
***************
*** 6962,6967 ****
--- 6962,7194 ----

    </sect1>

+  <sect1 id="functions-array">
+   <title>Array Functions</title>
+
+   <para>
+    <xref linkend="array-operators-table"> shows the operators
+    available for the <type>array</type> types.
+   </para>
+
+     <table id="array-operators-table">
+      <title><type>array</type> Operators</title>
+      <tgroup cols="4">
+       <thead>
+        <row>
+     <entry>Operator</entry>
+     <entry>Description</entry>
+     <entry>Example</entry>
+     <entry>Result</entry>
+        </row>
+       </thead>
+       <tbody>
+        <row>
+     <entry> <literal>=</literal> </entry>
+     <entry>equals</entry>
+     <entry><literal>ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]</literal></entry>
+     <entry><literal>t</literal></entry>
+        </row>
+        <row>
+     <entry> <literal>||</literal> </entry>
+     <entry>array-to-array concatenation</entry>
+     <entry><literal>ARRAY[1,2,3] || ARRAY[4,5,6]</literal></entry>
+     <entry><literal>{{1,2,3},{4,5,6}}</literal></entry>
+        </row>
+        <row>
+     <entry> <literal>||</literal> </entry>
+     <entry>array-to-array concatenation</entry>
+     <entry><literal>ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]</literal></entry>
+     <entry><literal>{{1,2,3},{4,5,6},{7,8,9}}</literal></entry>
+        </row>
+        <row>
+     <entry> <literal>||</literal> </entry>
+     <entry>element-to-array concatenation</entry>
+     <entry><literal>3 || ARRAY[4,5,6]</literal></entry>
+     <entry><literal>{3,4,5,6}</literal></entry>
+        </row>
+        <row>
+     <entry> <literal>||</literal> </entry>
+     <entry>array-to-element concatenation</entry>
+     <entry><literal>ARRAY[4,5,6] || 7</literal></entry>
+     <entry><literal>{4,5,6,7}</literal></entry>
+        </row>
+       </tbody>
+      </tgroup>
+     </table>
+
+   <para>
+    <xref linkend="array-functions-table"> shows the functions
+    available for use with array types. See <xref linkend="arrays">
+    for more discussion and examples for the use of these functions.
+   </para>
+
+     <table id="array-functions-table">
+      <title><type>array</type> Functions</title>
+      <tgroup cols="5">
+       <thead>
+        <row>
+     <entry>Function</entry>
+     <entry>Return Type</entry>
+     <entry>Description</entry>
+     <entry>Example</entry>
+     <entry>Result</entry>
+        </row>
+       </thead>
+       <tbody>
+        <row>
+     <entry>
+      <literal>
+       <function>array_accum</function>
+       (<type>anyarray</type>, <type>anyelement</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      append an element to the end of an array, ignoring
+      <literal>NULL</literal> elements, and creating an array if needed
+     </entry>
+     <entry><literal>array_accum(null, 1)</literal></entry>
+     <entry><literal>{1}</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_append</function>
+       (<type>anyarray</type>, <type>anyelement</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      append an element to the end of an array, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_append(ARRAY[1,2], 3)</literal></entry>
+     <entry><literal>{1,2,3}</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_assign</function>
+       (<type>anyarray</type>, <type>integer</type>, <type>anyelement</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      assign a value to a specific array element, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_assign(ARRAY[1,2,3], 2, 99)</literal></entry>
+     <entry><literal>{1,99,3}</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_cat</function>
+       (<type>anyarray</type>, <type>anyarray</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      concatenate two arrays, returning <literal>NULL</literal>
+      for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_cat(ARRAY[1,2,3], ARRAY[4,5,6])</literal></entry>
+     <entry><literal>{{1,2,3},{4,5,6}}</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_dims</function>
+       (<type>anyarray</type>)
+      </literal>
+     </entry>
+     <entry><type>text</type></entry>
+     <entry>
+      returns a text representation of array dimension lower and upper bounds,
+      generating an ERROR for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_dims(array[[1,2,3],[4,5,6]])</literal></entry>
+     <entry><literal>[1:2][1:3]</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_lower</function>
+       (<type>anyarray</type>, <type>integer</type>)
+      </literal>
+     </entry>
+     <entry><type>integer</type></entry>
+     <entry>
+      returns lower bound of the requested array dimension, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_lower(array_prepend(0, ARRAY[1,2,3]), 1)</literal></entry>
+     <entry><literal>0</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_prepend</function>
+       (<type>anyelement</type>, <type>anyarray</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      append an element to the beginning of an array, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_prepend(1, ARRAY[2,3])</literal></entry>
+     <entry><literal>{1,2,3}</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_subscript</function>
+       (<type>anyarray</type>, <type>integer</type>)
+      </literal>
+     </entry>
+     <entry><type>anyelement</type></entry>
+     <entry>
+      returns requested array element, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_subscript(ARRAY[1,2,3], 3)</literal></entry>
+     <entry><literal>3</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>array_upper</function>
+       (<type>anyarray</type>, <type>integer</type>)
+      </literal>
+     </entry>
+     <entry><type>integer</type></entry>
+     <entry>
+      returns upper bound of the requested array dimension, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>array_upper(array_append(ARRAY[1,2,3], 4), 1)</literal></entry>
+     <entry><literal>4</literal></entry>
+        </row>
+        <row>
+     <entry>
+      <literal>
+       <function>singleton_array</function>
+       (<type>anyelement</type>)
+      </literal>
+     </entry>
+     <entry><type>anyarray</type></entry>
+     <entry>
+      create an array from the provided element, returning
+      <literal>NULL</literal> for <literal>NULL</literal> inputs
+     </entry>
+     <entry><literal>singleton_array(1)</literal></entry>
+     <entry><literal>{1}</literal></entry>
+        </row>
+       </tbody>
+      </tgroup>
+     </table>
+   </sect1>

   <sect1 id="functions-aggregate">
    <title>Aggregate Functions</title>