array.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.46 2005/11/04 23:13:59 petere Exp $ -->

<sect1 id="arrays">
 <title>Arrays</title>

 <indexterm>
  <primary>array</primary>
 </indexterm>

 <para>
  <productname>PostgreSQL</productname> allows columns of a table to be
  defined as variable-length multidimensional arrays. Arrays of any
  built-in or user-defined base type can be created.  (Arrays of
  composite types or domains are not yet supported, however.)
 </para>

 <sect2>
  <title>Declaration of Array Types</title>

 <para>
  To illustrate the use of array types, we create this table:
<programlisting>
CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
</programlisting>
  As shown, an array data type is named by appending square brackets
  (<literal>[]</>) to the data type name of the array elements.  The
  above command will create a table named
  <structname>sal_emp</structname> with a column of type
  <type>text</type> (<structfield>name</structfield>), a
  one-dimensional array of type <type>integer</type>
  (<structfield>pay_by_quarter</structfield>), which represents the
  employee's salary by quarter, and a two-dimensional array of
  <type>text</type> (<structfield>schedule</structfield>), which
  represents the employee's weekly schedule.
 </para>

 <para>
  The syntax for <command>CREATE TABLE</command> allows the exact size of
  arrays to be specified, for example:

<programlisting>
CREATE TABLE tictactoe (
    squares   integer[3][3]
);
</programlisting>

  However, the current implementation does not enforce the array size
  limits &mdash; the behavior is the same as for arrays of unspecified
  length.
 </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
  of dimensions.  So, declaring number of dimensions or sizes in
  <command>CREATE TABLE</command> is simply documentation, it does not
  affect run-time behavior.
 </para>

 <para>
  An alternative syntax, which conforms to the SQL standard, may
  be used for one-dimensional arrays.
  <structfield>pay_by_quarter</structfield> could have been defined
  as:
<programlisting>
    pay_by_quarter  integer ARRAY[4],
</programlisting>
  This syntax requires an integer constant to denote the array size.
  As before, however, <productname>PostgreSQL</> does not enforce the
  size restriction.
 </para>
 </sect2>

 <sect2>
  <title>Array Value Input</title>

  <indexterm>
   <primary>array</primary>
   <secondary>constant</secondary>
  </indexterm>

  <para>
   To write an array value as a literal constant, enclose the element
   values within curly braces and separate them by commas.  (If you
   know C, this is not unlike the C syntax for initializing
   structures.)  You may put double quotes around any element value,
   and must do so if it contains commas or curly braces.  (More
   details appear below.)  Thus, the general format of an array
   constant is the following:
<synopsis>
'{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
</synopsis>
   where <replaceable>delim</replaceable> is the delimiter character
   for the type, as recorded in its <literal>pg_type</literal> entry.
   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 (<literal>,</>). Each <replaceable>val</replaceable> is
   either a constant of the array element type, or a subarray. An example
   of an array constant is
<programlisting>
'{{1,2,3},{4,5,6},{7,8,9}}'
</programlisting>
   This constant is a two-dimensional, 3-by-3 array consisting of
   three subarrays of integers.
  </para>

  <para>
   (These kinds of array constants are actually only a special case of
   the generic type constants discussed in <xref
   linkend="sql-syntax-constants-generic">.  The constant is initially
   treated as a string and passed to the array input conversion
   routine.  An explicit type specification might be necessary.)
  </para>

  <para>
   Now we can show some <command>INSERT</command> statements.

<programlisting>
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions
</programlisting>

  Note that multidimensional arrays must have matching extents for each
  dimension. A mismatch causes an error report.

<programlisting>
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
</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.  (This is likely to change in the future.)
  </para>

 <para>
  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,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
</programlisting>
 </para>

 <para>
  The <literal>ARRAY</> constructor syntax may also be used:
<programlisting>
INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
</programlisting>
  Notice that the array elements are ordinary SQL constants or
  expressions; for instance, string literals are single quoted, instead of
  double quoted as they would be in an array literal.  The <literal>ARRAY</>
  constructor syntax is discussed in more detail in
  <xref linkend="sql-syntax-array-constructors">.
 </para>
 </sect2>

 <sect2>
  <title>Accessing Arrays</title>

 <para>
  Now, we can run some queries on the table.
  First, we show how to access a single element of an array at a time.
  This query retrieves the names of the employees whose pay changed in
  the second quarter:
     
<programlisting>
SELECT name FROM sal_emp WHERE pay_by_quarter[1] &lt;&gt; pay_by_quarter[2];

 name
-------
 Carol
(1 row)
</programlisting>

  The array subscript numbers are written within square brackets.
  By default <productname>PostgreSQL</productname> uses the
  one-based numbering convention for arrays, that is,
  an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
  ends with <literal>array[<replaceable>n</>]</literal>.
 </para>

 <para>
  This query retrieves the third quarter pay of all employees:
     
<programlisting>
SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)
</programlisting>
 </para>

 <para>
  We can also access arbitrary rectangular slices of an array, or
  subarrays.  An array slice is denoted by writing
  <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
  for one or more array dimensions.  For example, this query retrieves the first
  item on Bill's schedule for the first two days of the week:
     
<programlisting>
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)
</programlisting>

  We could also have written

<programlisting>
SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
</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, as in this example:
<programlisting>
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)
</programlisting>
 </para>

 <para>
  Fetching from outside the current bounds of an array yields a
  SQL null value, not an error.  For example, if <literal>schedule</>
  currently has the dimensions <literal>[1:3][1:2]</> then referencing
  <literal>schedule[3][3]</> yields NULL.  Similarly, an array reference
  with the wrong number of subscripts yields a null rather than an error.
  Fetching an array slice that
  is completely outside the current bounds likewise yields a null array;
  but if the requested slice partially overlaps the array bounds, then it
  is silently reduced to just the overlapping region.
 </para>

 <para>
  The current dimensions of any array value can be retrieved with the
  <function>array_dims</function> function:

<programlisting>
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)
</programlisting>

  <function>array_dims</function> produces a <type>text</type> result,
  which is convenient for people to read but perhaps not so convenient
  for programs.  Dimensions can also be retrieved with
  <function>array_upper</function> and <function>array_lower</function>,
  which return the upper and lower bound of a
  specified array dimension, respectively.

<programlisting>
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)
</programlisting>
 </para>
 </sect2>

 <sect2>
  <title>Modifying Arrays</title>

 <para>
  An array value can be replaced completely:

<programlisting>
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';
</programlisting>

  or using the <literal>ARRAY</literal> expression syntax:

<programlisting>
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';
</programlisting>

  An array may also be updated at a single element: