diff options
| -rw-r--r-- | doc/src/sgml/array.sgml | 115 | ||||
| -rw-r--r-- | src/backend/utils/adt/arrayfuncs.c | 97 | ||||
| -rw-r--r-- | src/test/regress/expected/arrays.out | 34 | ||||
| -rw-r--r-- | src/test/regress/expected/domain.out | 26 | ||||
| -rw-r--r-- | src/test/regress/sql/domain.sql | 10 |
5 files changed, 193 insertions, 89 deletions
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index cbd576b2676..ae2d74e8672 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.35 2004/06/07 04:04:47 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.36 2004/08/05 03:29:11 joe Exp $ --> <sect1 id="arrays"> <title>Arrays</title> @@ -121,12 +121,23 @@ CREATE TABLE tictactoe ( INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', - '{{"meeting", "lunch"}, {}}'); + '{{"meeting", "lunch"}, {"meeting"}}'); +ERROR: multidimensional arrays must have array expressions with matching dimensions +</programlisting> + + Note that multidimensional arrays must have matching extents for each + dimension. A mismatch causes an error report. + +<programlisting> +INSERT INTO sal_emp + VALUES ('Bill', + '{10000, 10000, 10000, 10000}', + '{{"meeting", "lunch"}, {"training", "presentation"}}'); INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', - '{{"talk", "consult"}, {"meeting"}}'); + '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'); </programlisting> </para> @@ -138,59 +149,31 @@ INSERT INTO sal_emp </para> <para> - This can lead to surprising results. For example, the result of the - previous two inserts looks like this: + The result of the previous two inserts looks like this: <programlisting> SELECT * FROM sal_emp; - name | pay_by_quarter | schedule --------+---------------------------+-------------------- - Bill | {10000,10000,10000,10000} | {{meeting},{""}} - Carol | {20000,25000,25000,25000} | {{talk},{meeting}} + name | pay_by_quarter | schedule +-------+---------------------------+------------------------------------------- + Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}} + Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}} (2 rows) </programlisting> - Because the <literal>[2][2]</literal> element of - <structfield>schedule</structfield> is missing in each of the - <command>INSERT</command> statements, the <literal>[1][2]</literal> - element is discarded. </para> - <note> - <para> - Fixing this is on the to-do list. - </para> - </note> - <para> The <literal>ARRAY</literal> expression syntax may also be used: <programlisting> INSERT INTO sal_emp VALUES ('Bill', ARRAY[10000, 10000, 10000, 10000], - ARRAY[['meeting', 'lunch'], ['','']]); + ARRAY[['meeting', 'lunch'], ['training', 'presentation']]); INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], - ARRAY[['talk', 'consult'], ['meeting', '']]); -SELECT * FROM sal_emp; - name | pay_by_quarter | schedule --------+---------------------------+------------------------------- - Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}} - Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}} -(2 rows) + ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]); </programlisting> - Note that with this syntax, multidimensional arrays must have matching - extents for each dimension. A mismatch causes an error report, rather than - silently discarding values as in the previous case. - For example: -<programlisting> -INSERT INTO sal_emp - VALUES ('Carol', - ARRAY[20000, 25000, 25000, 25000], - ARRAY[['talk', 'consult'], ['meeting']]); -ERROR: multidimensional arrays must have array expressions with matching dimensions -</programlisting> - Also notice that the array elements are ordinary SQL constants or + Notice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal. The <literal>ARRAY</> expression syntax is discussed in more detail in <xref @@ -247,9 +230,9 @@ SELECT pay_by_quarter[3] FROM sal_emp; <programlisting> SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; - schedule --------------------- - {{meeting},{""}} + schedule +------------------------ + {{meeting},{training}} (1 row) </programlisting> @@ -266,9 +249,10 @@ SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill'; is specified, as in this example: <programlisting> SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; - schedule ---------------------------- - {{meeting,lunch},{"",""}} + + schedule +------------------------------------------- + {{meeting,lunch},{training,presentation}} (1 row) </programlisting> </para> @@ -547,6 +531,47 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); </para> <para> + By default, the lower bound index value of an array's dimensions is + set to one. If any of an array's dimensions has a lower bound index not + equal to one, an additional decoration that indicates the actual + array dimensions will precede the array structure decoration. + The decoration consists of square braces (<literal>[</> and <literal>]</>) + around each array dimension's lower and upper bound indicies, plus + a colon (<literal>:</>) delimiter character inbetween. Delimiting the + array dimension decoration from the array structure decoration is a + single assignment operator (<literal>=</>). For example: +<programlisting> +SELECT 1 || ARRAY[2,3] AS array; + + array +--------------- + [0:2]={1,2,3} +(1 row) + +SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array; + + array +-------------------------- + [0:1][1:2]={{1,2},{3,4}} +(1 row) +</programlisting> + </para> + + <para> + In a similar fashion, an array with non-default indicies may be specified + using the same literal syntax. For example: +<programlisting> +SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 + FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss; + + e1 | e2 +----+---- + 1 | 6 +(1 row) +</programlisting> + </para> + + <para> As shown previously, when writing an array value you may write double quotes around any individual array element. You <emphasis>must</> do so if the element value would otherwise diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index e9951d839a3..a8b1b636e08 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.105 2004/06/16 01:26:47 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.106 2004/08/05 03:29:37 joe Exp $ * *------------------------------------------------------------------------- */ @@ -217,7 +217,7 @@ array_in(PG_FUNCTION_ARGS) errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)", ndim, MAXDIM))); - for (q = p; isdigit((unsigned char) *q); q++); + for (q = p; isdigit((unsigned char) *q) || (*q == '-') || (*q == '+'); q++); if (q == p) /* no digits? */ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), @@ -229,7 +229,7 @@ array_in(PG_FUNCTION_ARGS) *q = '\0'; lBound[ndim] = atoi(p); p = q + 1; - for (q = p; isdigit((unsigned char) *q); q++); + for (q = p; isdigit((unsigned char) *q) || (*q == '-') || (*q == '+'); q++); if (q == p) /* no digits? */ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), @@ -270,6 +270,9 @@ array_in(PG_FUNCTION_ARGS) } else { + int ndim_braces, + dim_braces[MAXDIM]; + /* If array dimensions are given, expect '=' operator */ if (strncmp(p, ASSGN, strlen(ASSGN)) != 0) ereport(ERROR, @@ -278,6 +281,27 @@ array_in(PG_FUNCTION_ARGS) p += strlen(ASSGN); while (isspace((unsigned char) *p)) p++; + + /* + * intuit dimensions from brace structure -- it better match what + * we were given + */ + if (*p != '{') + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("array value must start with \"{\" or dimension information"))); + ndim_braces = ArrayCount(p, dim_braces, typdelim); + if (ndim_braces != ndim) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("array dimensions incompatible with array literal"))); + for (i = 0; i < ndim; ++i) + { + if (dim[i] != dim_braces[i]) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("array dimensions incompatible with array literal"))); + } } #ifdef ARRAYDEBUG @@ -303,7 +327,6 @@ array_in(PG_FUNCTION_ARGS) ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("missing left brace"))); - dataPtr = ReadArrayStr(p, nitems, ndim, dim, &my_extra->proc, typioparam, typmod, typdelim, typlen, typbyval, typalign, &nbytes); @@ -334,13 +357,18 @@ ArrayCount(char *str, int *dim, char typdelim) int nest_level = 0, i; int ndim = 1, - temp[MAXDIM]; + temp[MAXDIM], + nelems[MAXDIM], + nelems_last[MAXDIM]; bool scanning_string = false; bool eoArray = false; char *ptr; for (i = 0; i < MAXDIM; ++i) + { temp[i] = dim[i] = 0; + nelems_last[i] = nelems[i] = 1; + } if (strncmp(str, "{}", 2) == 0) return 0; @@ -394,6 +422,16 @@ ArrayCount(char *str, int *dim, char typdelim) (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("malformed array literal: \"%s\"", str))); nest_level--; + + if ((nelems_last[nest_level] != 1) && + (nelems[nest_level] != nelems_last[nest_level])) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("multidimensional arrays must have " + "array expressions with matching " + "dimensions"))); + nelems_last[nest_level] = nelems[nest_level]; + nelems[nest_level] = 1; if (nest_level == 0) eoArray = itemdone = true; else @@ -408,7 +446,10 @@ ArrayCount(char *str, int *dim, char typdelim) break; default: if (*ptr == typdelim && !scanning_string) + { itemdone = true; + nelems[nest_level - 1]++; + } break; } if (!itemdone) @@ -684,8 +725,15 @@ array_out(PG_FUNCTION_ARGS) char *p, *tmp, *retval, - **values; - bool *needquotes; + **values, + /* + * 33 per dim since we assume 15 digits per number + ':' +'[]' + * + * +2 allows for assignment operator + trailing null + */ + dims_str[(MAXDIM * 33) + 2]; + bool *needquotes, + needdims = false; int nitems, overall_length, i, @@ -693,7 +741,8 @@ array_out(PG_FUNCTION_ARGS) k, indx[MAXDIM]; int ndim, - *dim; + *dim, + *lb; ArrayMetaState *my_extra; element_type = ARR_ELEMTYPE(v); @@ -734,6 +783,7 @@ array_out(PG_FUNCTION_ARGS) ndim = ARR_NDIM(v); dim = ARR_DIMS(v); + lb = ARR_LBOUND(v); nitems = ArrayGetNItems(ndim, dim); if (nitems == 0) @@ -743,6 +793,19 @@ array_out(PG_FUNCTION_ARGS) } /* + * we will need to add explicit dimensions if any dimension + * has a lower bound other than one + */ + for (i = 0; i < ndim; i++) + { + if (lb[i] != 1) + { + needdims = true; + break; + } + } + + /* * Convert all values to string form, count total space needed * (including any overhead such as escaping backslashes), and detect * whether each item needs double quotes. @@ -798,12 +861,28 @@ array_out(PG_FUNCTION_ARGS) */ for (i = j = 0, k = 1; i < ndim; k *= dim[i++], j += k); - retval = (char *) palloc(overall_length + 2 * j); + /* add explicit dimensions if required */ + if (needdims) + { + char *ptr = dims_str; + + for (i = 0; i < ndim; i++) + { + sprintf(ptr, "[%d:%d]", lb[i], lb[i] + dim[i] - 1); + ptr += strlen(ptr); + } + *ptr++ = *ASSGN; + *ptr = '\0'; + } + + retval = (char *) palloc(strlen(dims_str) + overall_length + 2 * j); p = retval; #define APPENDSTR(str) (strcpy(p, (str)), p += strlen(p)) #define APPENDCHAR(ch) (*p++ = (ch), *p = '\0') + if (needdims) + APPENDSTR(dims_str); APPENDCHAR('{'); for (i = 0; i < ndim; indx[i++] = 0); j = 0; diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8647eccf14c..ea8e2b6e8f9 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -27,11 +27,11 @@ INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g) INSERT INTO arrtest (a, b[1:2], c, d[1:2]) VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}'); SELECT * FROM arrtest; - a | b | c | d | e | f | g --------------+-----------------+-----------+---------------+-----------+-----------------+------------- - {1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | {1.1,2.2} | {} | {} - {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde} - {} | {3,4} | {foo,bar} | {bar,foo} | | | + a | b | c | d | e | f | g +-------------+-----------------+-----------+---------------+-----------------+-----------------+------------- + {1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | [0:1]={1.1,2.2} | {} | {} + {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde} + {} | {3,4} | {foo,bar} | {bar,foo} | | | (3 rows) SELECT arrtest.a[1], @@ -184,9 +184,9 @@ SELECT array_append(array[42], 6) AS "{42,6}"; (1 row) SELECT array_prepend(6, array[42]) AS "{6,42}"; - {6,42} --------- - {6,42} + {6,42} +-------------- + [0:1]={6,42} (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; @@ -196,9 +196,9 @@ SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}"; - {{1,2},{3,4},{5,6}} ---------------------- - {{1,2},{3,4},{5,6}} + {{1,2},{3,4},{5,6}} +-------------------------------- + [0:2][1:2]={{1,2},{3,4},{5,6}} (1 row) SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}"; @@ -227,9 +227,9 @@ SELECT ARRAY[1,2] || 3 AS "{1,2,3}"; (1 row) SELECT 0 || ARRAY[1,2] AS "{0,1,2}"; - {0,1,2} ---------- - {0,1,2} + {0,1,2} +--------------- + [0:2]={0,1,2} (1 row) SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}"; @@ -257,9 +257,9 @@ SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; (1 row) SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; - {0,1,2,3} ------------ - {0,1,2,3} + {0,1,2,3} +----------------- + [0:3]={0,1,2,3} (1 row) -- array casts diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 39929de2b3f..750ebf7ccd8 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -72,29 +72,29 @@ create table domarrtest ( testint4arr domainint4arr , testtextarr domaintextarr ); -INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}}'); -INSERT INTO domarrtest values ('{{2,2}{2,2}}', '{{"a","b"}}'); -INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}{"e"}}'); -INSERT INTO domarrtest values ('{2,2}', '{{"a"}{"c"}}'); -INSERT INTO domarrtest values (NULL, '{{"a","b"}{"c","d","e"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}'); +INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}'); +INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}'); select * from domarrtest; testint4arr | testtextarr ---------------+--------------------- - {2,2} | {{a,c},{"",d}} - {{2,2},{0,2}} | {{a,b}} - {2,2} | {{a},{c},{e}} - {2,2} | {{c},{""}} - | {{a,c,""},{"",d,e}} + {2,2} | {{a,b},{c,d}} + {{2,2},{2,2}} | {{a,b}} + {2,2} | {{a,b},{c,d},{e,f}} + {2,2} | {{a},{c}} + | {{a,b,c},{d,e,f}} (5 rows) select testint4arr[1], testtextarr[2:2] from domarrtest; testint4arr | testtextarr -------------+------------- - 2 | {{"",d}} + 2 | {{c,d}} | + 2 | {{c,d}} 2 | {{c}} - 2 | {{""}} - | {{"",d,e}} + | {{d,e,f}} (5 rows) drop table domarrtest; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index 8e188636a5c..acbb9f80ea6 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -66,11 +66,11 @@ create table domarrtest ( testint4arr domainint4arr , testtextarr domaintextarr ); -INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}}'); -INSERT INTO domarrtest values ('{{2,2}{2,2}}', '{{"a","b"}}'); -INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}{"e"}}'); -INSERT INTO domarrtest values ('{2,2}', '{{"a"}{"c"}}'); -INSERT INTO domarrtest values (NULL, '{{"a","b"}{"c","d","e"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}'); +INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}'); +INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}'); +INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}'); select * from domarrtest; select testint4arr[1], testtextarr[2:2] from domarrtest; |