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

Commit0e13d62

Browse files
committed
Require that array literals produce "rectangular" arrays, i.e. all the
subarrays of a given dimension have the same number of elements/subarrays.Also repair a longstanding undocumented (as far as I can see) ability toexplicitly set array bounds in the array literal syntax. It now candeal properly with negative array indicies. Modify array_out so thatarrays with non-standard lower bounds (i.e. not 1) are output withthe expicit dimension syntax. This fixes a longstanding issue wherebyarrays with non-default lower bounds had them changed to defaultafter a dump/reload cycle.Modify regression tests and docs to suit, and add some minimaldocumentation regarding the explicit dimension syntax.
1 parent39ec59f commit0e13d62

File tree

5 files changed

+193
-89
lines changed

5 files changed

+193
-89
lines changed

‎doc/src/sgml/array.sgml

Lines changed: 70 additions & 45 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.35 2004/06/07 04:04:47 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.36 2004/08/05 03:29:11 joe Exp $ -->
22

33
<sect1 id="arrays">
44
<title>Arrays</title>
@@ -121,12 +121,23 @@ CREATE TABLE tictactoe (
121121
INSERT INTO sal_emp
122122
VALUES ('Bill',
123123
'{10000, 10000, 10000, 10000}',
124-
'{{"meeting", "lunch"}, {}}');
124+
'{{"meeting", "lunch"}, {"meeting"}}');
125+
ERROR: multidimensional arrays must have array expressions with matching dimensions
126+
</programlisting>
127+
128+
Note that multidimensional arrays must have matching extents for each
129+
dimension. A mismatch causes an error report.
130+
131+
<programlisting>
132+
INSERT INTO sal_emp
133+
VALUES ('Bill',
134+
'{10000, 10000, 10000, 10000}',
135+
'{{"meeting", "lunch"}, {"training", "presentation"}}');
125136

126137
INSERT INTO sal_emp
127138
VALUES ('Carol',
128139
'{20000, 25000, 25000, 25000}',
129-
'{{"talk", "consult"}, {"meeting"}}');
140+
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
130141
</programlisting>
131142
</para>
132143

@@ -138,59 +149,31 @@ INSERT INTO sal_emp
138149
</para>
139150

140151
<para>
141-
This can lead to surprising results. For example, the result of the
142-
previous two inserts looks like this:
152+
The result of the previous two inserts looks like this:
143153
<programlisting>
144154
SELECT * FROM sal_emp;
145-
name | pay_by_quarter | schedule
146-
-------+---------------------------+--------------------
147-
Bill | {10000,10000,10000,10000} | {{meeting},{""}}
148-
Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
155+
name | pay_by_quarter |schedule
156+
-------+---------------------------+-------------------------------------------
157+
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
158+
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
149159
(2 rows)
150160
</programlisting>
151-
Because the <literal>[2][2]</literal> element of
152-
<structfield>schedule</structfield> is missing in each of the
153-
<command>INSERT</command> statements, the <literal>[1][2]</literal>
154-
element is discarded.
155161
</para>
156162

157-
<note>
158-
<para>
159-
Fixing this is on the to-do list.
160-
</para>
161-
</note>
162-
163163
<para>
164164
The <literal>ARRAY</literal> expression syntax may also be used:
165165
<programlisting>
166166
INSERT INTO sal_emp
167167
VALUES ('Bill',
168168
ARRAY[10000, 10000, 10000, 10000],
169-
ARRAY[['meeting', 'lunch'], ['','']]);
169+
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
170170

171171
INSERT INTO sal_emp
172172
VALUES ('Carol',
173173
ARRAY[20000, 25000, 25000, 25000],
174-
ARRAY[['talk', 'consult'], ['meeting', '']]);
175-
SELECT * FROM sal_emp;
176-
name | pay_by_quarter | schedule
177-
-------+---------------------------+-------------------------------
178-
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
179-
Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
180-
(2 rows)
174+
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
181175
</programlisting>
182-
Note that with this syntax, multidimensional arrays must have matching
183-
extents for each dimension. A mismatch causes an error report, rather than
184-
silently discarding values as in the previous case.
185-
For example:
186-
<programlisting>
187-
INSERT INTO sal_emp
188-
VALUES ('Carol',
189-
ARRAY[20000, 25000, 25000, 25000],
190-
ARRAY[['talk', 'consult'], ['meeting']]);
191-
ERROR: multidimensional arrays must have array expressions with matching dimensions
192-
</programlisting>
193-
Also notice that the array elements are ordinary SQL constants or
176+
Notice that the array elements are ordinary SQL constants or
194177
expressions; for instance, string literals are single quoted, instead of
195178
double quoted as they would be in an array literal. The <literal>ARRAY</>
196179
expression syntax is discussed in more detail in <xref
@@ -247,9 +230,9 @@ SELECT pay_by_quarter[3] FROM sal_emp;
247230
<programlisting>
248231
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
249232

250-
schedule
251-
--------------------
252-
{{meeting},{""}}
233+
schedule
234+
------------------------
235+
{{meeting},{training}}
253236
(1 row)
254237
</programlisting>
255238

@@ -266,9 +249,10 @@ SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
266249
is specified, as in this example:
267250
<programlisting>
268251
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
269-
schedule
270-
---------------------------
271-
{{meeting,lunch},{"",""}}
252+
253+
schedule
254+
-------------------------------------------
255+
{{meeting,lunch},{training,presentation}}
272256
(1 row)
273257
</programlisting>
274258
</para>
@@ -546,6 +530,47 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
546530
up to the next right brace or delimiter is taken as the item value.
547531
</para>
548532

533+
<para>
534+
By default, the lower bound index value of an array's dimensions is
535+
set to one. If any of an array's dimensions has a lower bound index not
536+
equal to one, an additional decoration that indicates the actual
537+
array dimensions will precede the array structure decoration.
538+
The decoration consists of square braces (<literal>[</> and <literal>]</>)
539+
around each array dimension's lower and upper bound indicies, plus
540+
a colon (<literal>:</>) delimiter character inbetween. Delimiting the
541+
array dimension decoration from the array structure decoration is a
542+
single assignment operator (<literal>=</>). For example:
543+
<programlisting>
544+
SELECT 1 || ARRAY[2,3] AS array;
545+
546+
array
547+
---------------
548+
[0:2]={1,2,3}
549+
(1 row)
550+
551+
SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;
552+
553+
array
554+
--------------------------
555+
[0:1][1:2]={{1,2},{3,4}}
556+
(1 row)
557+
</programlisting>
558+
</para>
559+
560+
<para>
561+
In a similar fashion, an array with non-default indicies may be specified
562+
using the same literal syntax. For example:
563+
<programlisting>
564+
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
565+
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
566+
567+
e1 | e2
568+
----+----
569+
1 | 6
570+
(1 row)
571+
</programlisting>
572+
</para>
573+
549574
<para>
550575
As shown previously, when writing an array value you may write double
551576
quotes around any individual array

‎src/backend/utils/adt/arrayfuncs.c

Lines changed: 88 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.105 2004/06/16 01:26:47 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.106 2004/08/05 03:29:37 joe Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -217,7 +217,7 @@ array_in(PG_FUNCTION_ARGS)
217217
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
218218
ndim,MAXDIM)));
219219

220-
for (q=p;isdigit((unsignedchar)*q);q++);
220+
for (q=p;isdigit((unsignedchar)*q)|| (*q=='-')|| (*q=='+');q++);
221221
if (q==p)/* no digits? */
222222
ereport(ERROR,
223223
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
@@ -229,7 +229,7 @@ array_in(PG_FUNCTION_ARGS)
229229
*q='\0';
230230
lBound[ndim]=atoi(p);
231231
p=q+1;
232-
for (q=p;isdigit((unsignedchar)*q);q++);
232+
for (q=p;isdigit((unsignedchar)*q)|| (*q=='-')|| (*q=='+');q++);
233233
if (q==p)/* no digits? */
234234
ereport(ERROR,
235235
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
@@ -270,6 +270,9 @@ array_in(PG_FUNCTION_ARGS)
270270
}
271271
else
272272
{
273+
intndim_braces,
274+
dim_braces[MAXDIM];
275+
273276
/* If array dimensions are given, expect '=' operator */
274277
if (strncmp(p,ASSGN,strlen(ASSGN))!=0)
275278
ereport(ERROR,
@@ -278,6 +281,27 @@ array_in(PG_FUNCTION_ARGS)
278281
p+=strlen(ASSGN);
279282
while (isspace((unsignedchar)*p))
280283
p++;
284+
285+
/*
286+
* intuit dimensions from brace structure -- it better match what
287+
* we were given
288+
*/
289+
if (*p!='{')
290+
ereport(ERROR,
291+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
292+
errmsg("array value must start with \"{\" or dimension information")));
293+
ndim_braces=ArrayCount(p,dim_braces,typdelim);
294+
if (ndim_braces!=ndim)
295+
ereport(ERROR,
296+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
297+
errmsg("array dimensions incompatible with array literal")));
298+
for (i=0;i<ndim;++i)
299+
{
300+
if (dim[i]!=dim_braces[i])
301+
ereport(ERROR,
302+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
303+
errmsg("array dimensions incompatible with array literal")));
304+
}
281305
}
282306

283307
#ifdefARRAYDEBUG
@@ -303,7 +327,6 @@ array_in(PG_FUNCTION_ARGS)
303327
ereport(ERROR,
304328
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
305329
errmsg("missing left brace")));
306-
307330
dataPtr=ReadArrayStr(p,nitems,ndim,dim,&my_extra->proc,typioparam,
308331
typmod,typdelim,typlen,typbyval,typalign,
309332
&nbytes);
@@ -334,13 +357,18 @@ ArrayCount(char *str, int *dim, char typdelim)
334357
intnest_level=0,
335358
i;
336359
intndim=1,
337-
temp[MAXDIM];
360+
temp[MAXDIM],
361+
nelems[MAXDIM],
362+
nelems_last[MAXDIM];
338363
boolscanning_string= false;
339364
booleoArray= false;
340365
char*ptr;
341366

342367
for (i=0;i<MAXDIM;++i)
368+
{
343369
temp[i]=dim[i]=0;
370+
nelems_last[i]=nelems[i]=1;
371+
}
344372

345373
if (strncmp(str,"{}",2)==0)
346374
return0;
@@ -394,6 +422,16 @@ ArrayCount(char *str, int *dim, char typdelim)
394422
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
395423
errmsg("malformed array literal: \"%s\"",str)));
396424
nest_level--;
425+
426+
if ((nelems_last[nest_level]!=1)&&
427+
(nelems[nest_level]!=nelems_last[nest_level]))
428+
ereport(ERROR,
429+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
430+
errmsg("multidimensional arrays must have "
431+
"array expressions with matching "
432+
"dimensions")));
433+
nelems_last[nest_level]=nelems[nest_level];
434+
nelems[nest_level]=1;
397435
if (nest_level==0)
398436
eoArray=itemdone= true;
399437
else
@@ -408,7 +446,10 @@ ArrayCount(char *str, int *dim, char typdelim)
408446
break;
409447
default:
410448
if (*ptr==typdelim&& !scanning_string)
449+
{
411450
itemdone= true;
451+
nelems[nest_level-1]++;
452+
}
412453
break;
413454
}
414455
if (!itemdone)
@@ -684,16 +725,24 @@ array_out(PG_FUNCTION_ARGS)
684725
char*p,
685726
*tmp,
686727
*retval,
687-
**values;
688-
bool*needquotes;
728+
**values,
729+
/*
730+
* 33 per dim since we assume 15 digits per number + ':' +'[]'
731+
*
732+
* +2 allows for assignment operator + trailing null
733+
*/
734+
dims_str[(MAXDIM*33)+2];
735+
bool*needquotes,
736+
needdims= false;
689737
intnitems,
690738
overall_length,
691739
i,
692740
j,
693741
k,
694742
indx[MAXDIM];
695743
intndim,
696-
*dim;
744+
*dim,
745+
*lb;
697746
ArrayMetaState*my_extra;
698747

699748
element_type=ARR_ELEMTYPE(v);
@@ -734,6 +783,7 @@ array_out(PG_FUNCTION_ARGS)
734783

735784
ndim=ARR_NDIM(v);
736785
dim=ARR_DIMS(v);
786+
lb=ARR_LBOUND(v);
737787
nitems=ArrayGetNItems(ndim,dim);
738788

739789
if (nitems==0)
@@ -742,6 +792,19 @@ array_out(PG_FUNCTION_ARGS)
742792
PG_RETURN_CSTRING(retval);
743793
}
744794

795+
/*
796+
* we will need to add explicit dimensions if any dimension
797+
* has a lower bound other than one
798+
*/
799+
for (i=0;i<ndim;i++)
800+
{
801+
if (lb[i]!=1)
802+
{
803+
needdims= true;
804+
break;
805+
}
806+
}
807+
745808
/*
746809
* Convert all values to string form, count total space needed
747810
* (including any overhead such as escaping backslashes), and detect
@@ -798,12 +861,28 @@ array_out(PG_FUNCTION_ARGS)
798861
*/
799862
for (i=j=0,k=1;i<ndim;k *=dim[i++],j+=k);
800863

801-
retval= (char*)palloc(overall_length+2*j);
864+
/* add explicit dimensions if required */
865+
if (needdims)
866+
{
867+
char*ptr=dims_str;
868+
869+
for (i=0;i<ndim;i++)
870+
{
871+
sprintf(ptr,"[%d:%d]",lb[i],lb[i]+dim[i]-1);
872+
ptr+=strlen(ptr);
873+
}
874+
*ptr++=*ASSGN;
875+
*ptr='\0';
876+
}
877+
878+
retval= (char*)palloc(strlen(dims_str)+overall_length+2*j);
802879
p=retval;
803880

804881
#defineAPPENDSTR(str)(strcpy(p, (str)), p += strlen(p))
805882
#defineAPPENDCHAR(ch)(*p++ = (ch), *p = '\0')
806883

884+
if (needdims)
885+
APPENDSTR(dims_str);
807886
APPENDCHAR('{');
808887
for (i=0;i<ndim;indx[i++]=0);
809888
j=0;

0 commit comments

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