7 This must become a chapter on array behavior. Volunteers? - thomas 1998-01-12
13 <ProductName>Postgres</ProductName> allows attributes of an instance to be defined
14 as fixed-length or variable-length multi-dimensional
15 arrays. Arrays of any base type or user-defined type
16 can be created. To illustrate their use, we first create a
17 class with arrays of base types.
20 CREATE TABLE SAL_EMP (
22 pay_by_quarter int4[],
29 The above query will create a class named SAL_EMP with
30 a <FirstTerm>text</FirstTerm> string (name), a one-dimensional array of <FirstTerm>int4</FirstTerm>
31 (pay_by_quarter), which represents the employee's
32 salary by quarter and a two-dimensional array of <FirstTerm>text</FirstTerm>
33 (schedule), which represents the employee's weekly
34 schedule. Now we do some <FirstTerm>INSERTS</FirstTerm>s; note that when
35 appending to an array, we enclose the values within
36 braces and separate them by commas. If you know <FirstTerm>C</FirstTerm>,
37 this is not unlike the syntax for initializing structures.
42 '{10000, 10000, 10000, 10000}',
43 '{{"meeting", "lunch"}, {}}');
47 '{20000, 25000, 25000, 25000}',
48 '{{"talk", "consult"}, {"meeting"}}');
51 By default, <ProductName>Postgres</ProductName> uses the "one-based" numbering
52 convention for arrays -- that is, an array of n elements starts with array[1] and ends with array[n].
53 Now, we can run some queries on SAL_EMP. First, we
54 show how to access a single element of an array at a
55 time. This query retrieves the names of the employees
56 whose pay changed in the second quarter:
61 WHERE SAL_EMP.pay_by_quarter[1] <>
62 SAL_EMP.pay_by_quarter[2];
73 This query retrieves the third quarter pay of all
77 SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP;
91 We can also access arbitrary slices of an array, or
92 subarrays. This query retrieves the first item on
93 Bill's schedule for the first two days of the week.
96 SELECT SAL_EMP.schedule[1:2][1:1]
98 WHERE SAL_EMP.name = 'Bill';
100 +-------------------+
102 +-------------------+
103 |{{"meeting"},{""}} |
104 +-------------------+