7 This must become a chapter on array behavior. Volunteers? - thomas 1998-01-12
13 <ProductName>Postgres</ProductName> allows attributes of a class
14 to be defined as variable-length multi-dimensional
15 arrays. Arrays of any built-in type or user-defined type
16 can be created. To illustrate their use, we create this class:
19 CREATE TABLE sal_emp (
21 pay_by_quarter int4[],
28 The above query will create a class named <FirstTerm>sal_emp</FirstTerm> with
29 a <FirstTerm>text</FirstTerm> string (name), a one-dimensional array of <FirstTerm>int4</FirstTerm>
30 (pay_by_quarter), which represents the employee's
31 salary by quarter, and a two-dimensional array of <FirstTerm>text</FirstTerm>
32 (schedule), which represents the employee's weekly
33 schedule. Now we do some <FirstTerm>INSERTS</FirstTerm>s; note that when
34 appending to an array, we enclose the values within
35 braces and separate them by commas. If you know <FirstTerm>C</FirstTerm>,
36 this is not unlike the syntax for initializing structures.
41 '{10000, 10000, 10000, 10000}',
42 '{{"meeting", "lunch"}, {}}');
46 '{20000, 25000, 25000, 25000}',
47 '{{"talk", "consult"}, {"meeting"}}');
50 Now, we can run some queries on sal_emp. First, we
51 show how to access a single element of an array at a
52 time. This query retrieves the names of the employees
53 whose pay changed in the second quarter:
56 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
64 <ProductName>Postgres</ProductName> uses the "one-based" numbering
65 convention for arrays --- that is, an array of n elements starts with
66 array[1] and ends with array[n].
70 This query retrieves the third quarter pay of all
74 SELECT pay_by_quarter[3] FROM sal_emp;
85 We can also access arbitrary slices of an array, or
86 subarrays. An array slice is denoted by writing
87 "lower subscript : upper subscript" for one or more array
88 dimensions. This query retrieves the first item on
89 Bill's schedule for the first two days of the week:
92 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
100 We could also have written
103 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
106 with the same result.
110 An array value can be replaced completely:
113 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
114 WHERE name = 'Carol';
117 or updated at a single entry:
120 UPDATE sal_emp SET pay_by_quarter[4] = 15000
124 or updated in a slice:
127 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
128 WHERE name = 'Carol';
133 An array can be enlarged by assigning to an element adjacent to
134 those already present, or by assigning to a slice that is adjacent
135 to or overlaps the data already present. Currently, this is only
136 allowed for one-dimensional arrays, not multidimensional arrays.
137 For example, if an array value currently has 4 elements, it will
138 have five elements after an update that assigns to array[5].
142 The syntax for CREATE TABLE allows fixed-length arrays to be
146 CREATE TABLE tictactoe (
151 However, the current implementation does not enforce the array
152 size limits --- the behavior is the same as for arrays of
157 Actually, the current implementation doesn't enforce the declared
158 number of dimensions either. Arrays of a particular base type
159 are all considered to be of the same type, regardless of size or
160 number of dimensions.