diff options
Diffstat (limited to 'doc/src/sgml/array.sgml')
-rw-r--r-- | doc/src/sgml/array.sgml | 110 |
1 files changed, 58 insertions, 52 deletions
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 2d179fd7f16..c24646e43ca 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.46 2005/11/04 23:13:59 petere Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.47 2005/11/17 22:14:50 tgl Exp $ --> <sect1 id="arrays"> <title>Arrays</title> @@ -111,6 +111,13 @@ CREATE TABLE tictactoe ( </para> <para> + To set an element of an array constant to NULL, write <literal>NULL</> + for the element value. (Any upper- or lower-case variant of + <literal>NULL</> will do.) If you want an actual string value + <quote>NULL</>, you must put double quotes around it. + </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 @@ -125,17 +132,6 @@ CREATE TABLE tictactoe ( 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 @@ -145,15 +141,9 @@ INSERT INTO sal_emp </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 @@ -183,6 +173,19 @@ INSERT INTO sal_emp constructor syntax is discussed in more detail in <xref linkend="sql-syntax-array-constructors">. </para> + + <para> + Multidimensional arrays must have matching extents for each + dimension. A mismatch causes an error report, for example: + +<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> + </para> </sect2> <sect2> @@ -262,14 +265,22 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; </para> <para> - Fetching from outside the current bounds of an array yields a - SQL null value, not an error. For example, if <literal>schedule</> + An array subscript expression will return null if either the array itself or + any of the subscript expressions are null. Also, null is returned if a + subscript is outside the array bounds (this case does not raise 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 + </para> + + <para> + An array slice expression likewise yields null if the array itself or + any of the subscript expressions are null. However, in other corner + cases such as selecting an array slice that + is completely outside the current array bounds, a slice expression + yields an empty (zero-dimensional) array instead of null. + If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region. </para> @@ -349,7 +360,7 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' </para> <para> - Array slice assignment allows creation of arrays that do not use one-based + Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to <literal>myarray[-2:7]</> to create an array with subscript values running from -2 to 7. </para> @@ -442,7 +453,7 @@ SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); arrays, but <function>array_cat</function> supports multidimensional arrays. Note that the concatenation operator discussed above is preferred over - direct use of these functions. In fact, the functions are primarily for use + direct use of these functions. In fact, the functions exist primarily for use in implementing the concatenation operator. However, they may be directly useful in the creation of user-defined aggregates. Some examples: @@ -544,8 +555,9 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); <para> The array output routine will put double quotes around element values - if they are empty strings or contain curly braces, delimiter characters, - double quotes, backslashes, or white space. Double quotes and backslashes + if they are empty strings, contain curly braces, delimiter characters, + double quotes, backslashes, or white space, or match the word + <literal>NULL</>. Double quotes and backslashes embedded in element values will be backslash-escaped. For numeric data types it is safe to assume that double quotes will never appear, but for textual data types one should be prepared to cope with either presence @@ -555,35 +567,15 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); <para> By default, the lower bound index value of an array's dimensions is - set to one. If any of an array's dimensions has a lower bound index not - equal to one, an additional decoration that indicates the actual - array dimensions will precede the array structure decoration. + set to one. To represent arrays with other lower bounds, the array + subscript ranges can be specified explicitly before writing the + array contents. This decoration consists of square brackets (<literal>[]</>) around each array dimension's lower and upper bounds, with a colon (<literal>:</>) delimiter character in between. The array dimension decoration is followed by an equal sign (<literal>=</>). For example: <programlisting> -SELECT 1 || ARRAY[2,3] AS array; - - array ---------------- - [0:2]={1,2,3} -(1 row) - -SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array; - - array --------------------------- - [0:1][1:2]={{1,2},{3,4}} -(1 row) -</programlisting> - </para> - - <para> - This syntax can also be used to specify non-default array subscripts - in an array literal. For example: -<programlisting> SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss; @@ -592,6 +584,18 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 1 | 6 (1 row) </programlisting> + The array output routine will include explicit dimensions in its result + only when there are one or more lower bounds different from one. + </para> + + <para> + If the value written for an element is <literal>NULL</> (in any case + variant), the element is taken to be NULL. The presence of any quotes + or backslashes disables this and allows the literal string value + <quote>NULL</> to be entered. Also, for backwards compatibility with + pre-8.2 versions of <productname>PostgreSQL</>, the <xref + linkend="guc-array-nulls"> configuration parameter may be turned + <literal>off</> to suppress recognition of <literal>NULL</> as a NULL. </para> <para> @@ -600,7 +604,9 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or whatever the delimiter character is), double quotes, backslashes, or leading or trailing - whitespace must be double-quoted. To put a double quote or backslash in a + whitespace must be double-quoted. Empty strings and strings matching the + word <literal>NULL</> must be quoted, too. To put a double quote or + backslash in a quoted array element value, precede it with a backslash. Alternatively, you can use backslash-escaping to protect all data characters that would otherwise be taken as array syntax. |