1- <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.26 2003/06/24 23:14:42 momjian Exp $ -->
1+ <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.27 2003/06/25 21:30:25 momjian Exp $ -->
22
33<sect1 id="arrays">
44 <title>Arrays</title>
@@ -60,74 +60,14 @@ INSERT INTO sal_emp
6060</programlisting>
6161 </para>
6262
63- <para>
64- A limitation of the present array implementation is that individual
65- elements of an array cannot be SQL null values. The entire array can be set
66- to null, but you can't have an array with some elements null and some
67- not.
68- </para>
69- <para>
70- This can lead to surprising results. For example, the result of the
71- previous two inserts looks like this:
72- <programlisting>
73- SELECT * FROM sal_emp;
74- name | pay_by_quarter | schedule
75- -------+---------------------------+--------------------
76- Bill | {10000,10000,10000,10000} | {{meeting},{""}}
77- Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
78- (2 rows)
79- </programlisting>
80- Because the <literal>[2][2]</literal> element of
81- <structfield>schedule</structfield> is missing in each of the
82- <command>INSERT</command> statements, the <literal>[1][2]</literal>
83- element is discarded.
84- </para>
85-
8663 <note>
8764 <para>
88- Fixing this is on the to-do list.
65+ A limitation of the present array implementation is that individual
66+ elements of an array cannot be SQL null values. The entire array can be set
67+ to null, but you can't have an array with some elements null and some
68+ not. Fixing this is on the to-do list.
8969 </para>
9070 </note>
91-
92- <para>
93- The <command>ARRAY</command> expression syntax may also be used:
94- <programlisting>
95- INSERT INTO sal_emp
96- VALUES ('Bill',
97- ARRAY[10000, 10000, 10000, 10000],
98- ARRAY[['meeting', 'lunch'], ['','']]);
99-
100- INSERT INTO sal_emp
101- VALUES ('Carol',
102- ARRAY[20000, 25000, 25000, 25000],
103- ARRAY[['talk', 'consult'], ['meeting', '']]);
104- SELECT * FROM sal_emp;
105- name | pay_by_quarter | schedule
106- -------+---------------------------+-------------------------------
107- Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
108- Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
109- (2 rows)
110- </programlisting>
111- Note that with this syntax, multidimensional arrays must have matching
112- extents for each dimension. This eliminates the missing-array-elements
113- problem above. For example:
114- <programlisting>
115- INSERT INTO sal_emp
116- VALUES ('Carol',
117- ARRAY[20000, 25000, 25000, 25000],
118- ARRAY[['talk', 'consult'], ['meeting']]);
119- ERROR: Multidimensional arrays must have array expressions with matching dimensions
120- </programlisting>
121- Also notice that string literals are single quoted instead of double quoted.
122- </para>
123-
124- <note>
125- <para>
126- The examples in the rest of this section are based on the
127- <command>ARRAY</command> expression syntax <command>INSERT</command>s.
128- </para>
129- </note>
130-
13171 </sect2>
13272
13373 <sect2>
@@ -192,30 +132,11 @@ SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
192132</programlisting>
193133
194134 with the same result. An array subscripting operation is always taken to
195- represent an array slice if any of the subscripts are written in the form
135+ represent an array slice if any of the subscripts are written in the
136+ form
196137 <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
197138 A lower bound of 1 is assumed for any subscript where only one value
198- is specified; another example follows:
199- <programlisting>
200- SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
201- schedule
202- ---------------------------
203- {{meeting,lunch},{"",""}}
204- (1 row)
205- </programlisting>
206- </para>
207-
208- <para>
209- Additionally, we can also access a single arbitrary array element of
210- a one-dimensional array with the <function>array_subscript</function>
211- function:
212- <programlisting>
213- SELECT array_subscript(pay_by_quarter, 2) FROM sal_emp WHERE name = 'Bill';
214- array_subscript
215- -----------------
216- 10000
217- (1 row)
218- </programlisting>
139+ is specified.
219140 </para>
220141
221142 <para>
@@ -226,23 +147,7 @@ UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
226147 WHERE name = 'Carol';
227148</programlisting>
228149
229- or using the <command>ARRAY</command> expression syntax:
230-
231- <programlisting>
232- UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
233- WHERE name = 'Carol';
234- </programlisting>
235-
236- <note>
237- <para>
238- Anywhere you can use the <quote>curly braces</quote> array syntax,
239- you can also use the <command>ARRAY</command> expression syntax. The
240- remainder of this section will illustrate only one or the other, but
241- not both.
242- </para>
243- </note>
244-
245- An array may also be updated at a single element:
150+ or updated at a single element:
246151
247152<programlisting>
248153UPDATE sal_emp SET pay_by_quarter[4] = 15000
@@ -255,14 +160,6 @@ UPDATE sal_emp SET pay_by_quarter[4] = 15000
255160UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
256161 WHERE name = 'Carol';
257162</programlisting>
258-
259- A one-dimensional array may also be updated with the
260- <function>array_assign</function> function:
261-
262- <programlisting>
263- UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000)
264- WHERE name = 'Bill';
265- </programListing>
266163 </para>
267164
268165 <para>
@@ -281,88 +178,6 @@ UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000)
281178 create an array with subscript values running from -2 to 7.
282179 </para>
283180
284- <para>
285- An array can also be enlarged by using the concatenation operator,
286- <command>||</command>.
287- <programlisting>
288- SELECT ARRAY[1,2] || ARRAY[3,4];
289- ?column?
290- ---------------
291- {{1,2},{3,4}}
292- (1 row)
293-
294- SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
295- ?column?
296- ---------------------
297- {{5,6},{1,2},{3,4}}
298- (1 row)
299- </programlisting>
300-
301- The concatenation operator allows a single element to be pushed on to the
302- beginning or end of a one-dimensional array. It also allows two
303- <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
304- and an <replaceable>N+1</>-dimensional array. In the former case, the two
305- <replaceable>N</>-dimension arrays become outer elements of an
306- <replaceable>N+1</>-dimensional array. In the latter, the
307- <replaceable>N</>-dimensional array is added as either the first or last
308- outer element of the <replaceable>N+1</>-dimensional array.
309-
310- The array is extended in the direction of the push. Hence, by pushing
311- onto the beginning of an array with a one-based subscript, a zero-based
312- subscript array is created:
313-
314- <programlisting>
315- SELECT array_dims(t.f) FROM (SELECT 1 || ARRAY[2,3] AS f) AS t;
316- array_dims
317- ------------
318- [0:2]
319- (1 row)
320- </programlisting>
321- </para>
322-
323- <para>
324- An array can also be enlarged by using the functions
325- <function>array_prepend</function>, <function>array_append</function>,
326- or <function>array_cat</function>. The first two only support one-dimensional
327- arrays, but <function>array_cat</function> supports multidimensional arrays.
328-
329- Note that the concatenation operator discussed above is preferred over
330- direct use of these functions. In fact, the functions are primarily for use
331- in implementing the concatenation operator. However, they may be directly
332- useful in the creation of user-defined aggregates. Some examples:
333-
334- <programlisting>
335- SELECT array_prepend(1, ARRAY[2,3]);
336- array_prepend
337- ---------------
338- {1,2,3}
339- (1 row)
340-
341- SELECT array_append(ARRAY[1,2], 3);
342- array_append
343- --------------
344- {1,2,3}
345- (1 row)
346-
347- SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
348- array_cat
349- ---------------
350- {{1,2},{3,4}}
351- (1 row)
352-
353- SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
354- array_cat
355- ---------------------
356- {{1,2},{3,4},{5,6}}
357- (1 row)
358-
359- SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
360- array_cat
361- ---------------------
362- {{5,6},{1,2},{3,4}}
363- </programlisting>
364- </para>
365-
366181 <para>
367182 The syntax for <command>CREATE TABLE</command> allows fixed-length
368183 arrays to be defined:
@@ -378,16 +193,6 @@ CREATE TABLE tictactoe (
378193 length.
379194 </para>
380195
381- <para>
382- An alternative syntax for one-dimensional arrays may be used.
383- <structfield>pay_by_quarter</structfield> could have been defined as:
384- <programlisting>
385- pay_by_quarter integer ARRAY[4],
386- </programlisting>
387- This syntax may <emphasis>only</emphasis> be used with the integer
388- constant to denote the array size.
389- </para>
390-
391196 <para>
392197 Actually, the current implementation does not enforce the declared
393198 number of dimensions either. Arrays of a particular element type are
@@ -495,72 +300,6 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;
495300 is not ignored, however: after skipping leading whitespace, everything
496301 up to the next right brace or delimiter is taken as the item value.
497302 </para>
498-
499- <para>
500- As illustrated earlier in this chapter, arrays may also be represented
501- using the <command>ARRAY</command> expression syntax. This representation
502- of an array value consists of items that are interpreted according to the
503- I/O conversion rules for the array's element type, plus decoration that
504- indicates the array structure. The decoration consists of the keyword
505- <command>ARRAY</command> and square brackets (<literal>[</> and
506- <literal>]</>) around the array values, plus delimiter characters between
507- adjacent items. The delimiter character is always a comma (<literal>,</>).
508- When representing multidimensional arrays, the keyword
509- <command>ARRAY</command> is only necessary for the outer level. For example,
510- <literal>'{{"hello world", "happy birthday"}}'</literal> could be written as:
511- <programlisting>
512- SELECT ARRAY[['hello world', 'happy birthday']];
513- array
514- ------------------------------------
515- {{"hello world","happy birthday"}}
516- (1 row)
517- </programlisting>
518- or it also could be written as:
519- <programlisting>
520- SELECT ARRAY[ARRAY['hello world', 'happy birthday']];
521- array
522- ------------------------------------
523- {{"hello world","happy birthday"}}
524- (1 row)
525- </programlisting>
526- </para>
527-
528- <para>
529- A final method to represent an array, is through an
530- <command>ARRAY</command> sub-select expression. For example:
531- <programlisting>
532- SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
533- ?column?
534- -------------------------------------------------------------
535- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
536- (1 row)
537- </programlisting>
538- The sub-select may <emphasis>only</emphasis> return a single column. The
539- resulting one-dimensional array will have an element for each row in the
540- sub-select result, with an element type matching that of the sub-select's
541- target column.
542- </para>
543-
544- <para>
545- Arrays may be cast from one type to another in similar fashion to other
546- data types:
547-
548- <programlisting>
549- SELECT ARRAY[1,2,3]::oid[];
550- array
551- ---------
552- {1,2,3}
553- (1 row)
554-
555- SELECT CAST(ARRAY[1,2,3] AS float8[]);
556- array
557- ---------
558- {1,2,3}
559- (1 row)
560- </programlisting>
561-
562- </para>
563-
564303 </sect2>
565304
566305 <sect2>
@@ -578,14 +317,6 @@ SELECT CAST(ARRAY[1,2,3] AS float8[]);
578317 that would otherwise be taken as array syntax or ignorable white space.
579318 </para>
580319
581- <note>
582- <para>
583- The discussion in the preceding paragraph with respect to double quoting does
584- not pertain to the <command>ARRAY</command> expression syntax. In that case,
585- each element is quoted exactly as any other literal value of the element type.
586- </para>
587- </note>
588-
589320 <para>
590321 The array output routine will put double quotes around element values
591322 if they are empty strings or contain curly braces, delimiter characters,