1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
<Chapter Id="arrays">
<Title>Arrays</Title>
<Para>
<Note>
<Para>
This must become a chapter on array behavior. Volunteers? - thomas 1998-01-12
</Para>
</Note>
</Para>
<Para>
<ProductName>Postgres</ProductName> allows attributes of an instance to be defined
as fixed-length or variable-length multi-dimensional
arrays. Arrays of any base type or user-defined type
can be created. To illustrate their use, we first create a
class with arrays of base types.
<ProgramListing>
CREATE TABLE SAL_EMP (
name text,
pay_by_quarter int4[],
schedule text[][]
);
</ProgramListing>
</Para>
<Para>
The above query will create a class named SAL_EMP with
a <FirstTerm>text</FirstTerm> string (name), a one-dimensional array of <FirstTerm>int4</FirstTerm>
(pay_by_quarter), which represents the employee's
salary by quarter and a two-dimensional array of <FirstTerm>text</FirstTerm>
(schedule), which represents the employee's weekly
schedule. Now we do some <FirstTerm>INSERTS</FirstTerm>s; note that when
appending to an array, we enclose the values within
braces and separate them by commas. If you know <FirstTerm>C</FirstTerm>,
this is not unlike the syntax for initializing structures.
<ProgramListing>
INSERT INTO SAL_EMP
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {}}');
INSERT INTO SAL_EMP
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"talk", "consult"}, {"meeting"}}');
</ProgramListing>
By default, <ProductName>Postgres</ProductName> uses the "one-based" numbering
convention for arrays -- that is, an array of n elements starts with array[1] and ends with array[n].
Now, we can run some queries on SAL_EMP. 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] <> pay_by_quarter[2];
name
-------
Carol
(1 row)
</ProgramListing>
</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 slices of an array, or
subarrays. 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"},{""}}
(1 row)
</ProgramListing>
</Para>
</Chapter>
|
