Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb3c0551

Browse files
committed
Create real array comparison functions (that use the element datatype's
comparison functions), replacing the highly bogus bitwise array_eq. Createa btree index opclass for ANYARRAY --- it is now possible to create indexeson array columns.Arrange to cache the results of catalog lookups across multiple arrayoperations, instead of repeating the lookups on every call.Add string_to_array and array_to_string functions.Remove singleton_array, array_accum, array_assign, and array_subscriptfunctions, since these were for proof-of-concept and not intended to becomesupported functions.Minor adjustments to behavior in some corner cases with empty orzero-dimensional arrays.Joe Conway (with some editorializing by Tom Lane).
1 parent0c985ab commitb3c0551

File tree

28 files changed

+1731
-557
lines changed

28 files changed

+1731
-557
lines changed

‎doc/src/sgml/array.sgml

Lines changed: 278 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.27 2003/06/25 21:30:25momjian Exp $ -->
1+
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.28 2003/06/27 00:33:25tgl Exp $ -->
22

33
<sect1 id="arrays">
44
<title>Arrays</title>
@@ -60,14 +60,74 @@ 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+
6386
<note>
6487
<para>
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.
88+
Fixing this is on the to-do list.
6989
</para>
7090
</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+
71131
</sect2>
72132

73133
<sect2>
@@ -132,11 +192,30 @@ SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
132192
</programlisting>
133193

134194
with the same result. An array subscripting operation is always taken to
135-
represent an array slice if any of the subscripts are written in the
136-
form
195+
represent an array slice if any of the subscripts are written in the form
137196
<literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
138197
A lower bound of 1 is assumed for any subscript where only one value
139-
is specified.
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>
140219
</para>
141220

142221
<para>
@@ -147,7 +226,23 @@ UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
147226
WHERE name = 'Carol';
148227
</programlisting>
149228

150-
or updated at a single element:
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:
151246

152247
<programlisting>
153248
UPDATE sal_emp SET pay_by_quarter[4] = 15000
@@ -160,6 +255,14 @@ UPDATE sal_emp SET pay_by_quarter[4] = 15000
160255
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
161256
WHERE name = 'Carol';
162257
</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>
163266
</para>
164267

165268
<para>
@@ -178,6 +281,88 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
178281
create an array with subscript values running from -2 to 7.
179282
</para>
180283

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+
181366
<para>
182367
The syntax for <command>CREATE TABLE</command> allows fixed-length
183368
arrays to be defined:
@@ -193,6 +378,16 @@ CREATE TABLE tictactoe (
193378
length.
194379
</para>
195380

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+
196391
<para>
197392
Actually, the current implementation does not enforce the declared
198393
number of dimensions either. Arrays of a particular element type are
@@ -300,6 +495,72 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;
300495
is not ignored, however: after skipping leading whitespace, everything
301496
up to the next right brace or delimiter is taken as the item value.
302497
</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+
303564
</sect2>
304565

305566
<sect2>
@@ -317,6 +578,14 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;
317578
that would otherwise be taken as array syntax or ignorable white space.
318579
</para>
319580

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+
320589
<para>
321590
The array output routine will put double quotes around element values
322591
if they are empty strings or contain curly braces, delimiter characters,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp