aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/array.sgml27
-rw-r--r--src/backend/executor/execQual.c22
-rw-r--r--src/backend/nodes/copyfuncs.c1
-rw-r--r--src/backend/nodes/equalfuncs.c1
-rw-r--r--src/backend/nodes/outfuncs.c1
-rw-r--r--src/backend/parser/gram.y11
-rw-r--r--src/backend/parser/parse_node.c59
-rw-r--r--src/backend/parser/parse_target.c2
-rw-r--r--src/backend/utils/adt/arrayfuncs.c33
-rw-r--r--src/backend/utils/adt/ruleutils.c2
-rw-r--r--src/include/nodes/parsenodes.h17
-rw-r--r--src/include/nodes/primnodes.h5
-rw-r--r--src/include/utils/array.h2
-rw-r--r--src/test/regress/expected/arrays.out111
-rw-r--r--src/test/regress/sql/arrays.sql41
15 files changed, 302 insertions, 33 deletions
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index 4385a09cd97..58878451f00 100644
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -277,6 +277,29 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
</para>
<para>
+ It is possible to omit the <replaceable>lower-bound</replaceable> and/or
+ <replaceable>upper-bound</replaceable> of a slice specifier; the missing
+ bound is replaced by the lower or upper limit of the array's subscripts.
+ For example:
+
+<programlisting>
+SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
+
+ schedule
+------------------------
+ {{lunch},{presentation}}
+(1 row)
+
+SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
+
+ schedule
+------------------------
+ {{meeting},{training}}
+(1 row)
+</programlisting>
+ </para>
+
+ <para>
An array subscript expression will return null if either the array itself or
any of the subscript expressions are null. Also, null is returned if a
subscript is outside the array bounds (this case does not raise an error).
@@ -391,6 +414,10 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';
</programlisting>
+ The slice syntaxes with omitted <replaceable>lower-bound</replaceable> and/or
+ <replaceable>upper-bound</replaceable> can be used too, but only when
+ updating an array value that is not NULL or zero-dimensional (otherwise,
+ there is no existing subscript limit to substitute).
</para>
<para>
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index 29f058ce5cb..d4dc2dcd21a 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -271,6 +271,8 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
j = 0;
IntArray upper,
lower;
+ bool upperProvided[MAXDIM],
+ lowerProvided[MAXDIM];
int *lIndex;
array_source = ExecEvalExpr(astate->refexpr,
@@ -300,6 +302,15 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
i + 1, MAXDIM)));
+ if (eltstate == NULL)
+ {
+ /* Slice bound is omitted, so use array's upper bound */
+ Assert(astate->reflowerindexpr != NIL);
+ upperProvided[i++] = false;
+ continue;
+ }
+ upperProvided[i] = true;
+
upper.indx[i++] = DatumGetInt32(ExecEvalExpr(eltstate,
econtext,
&eisnull,
@@ -328,6 +339,14 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
j + 1, MAXDIM)));
+ if (eltstate == NULL)
+ {
+ /* Slice bound is omitted, so use array's lower bound */
+ lowerProvided[j++] = false;
+ continue;
+ }
+ lowerProvided[j] = true;
+
lower.indx[j++] = DatumGetInt32(ExecEvalExpr(eltstate,
econtext,
&eisnull,
@@ -398,6 +417,7 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
econtext->caseValue_datum =
array_get_slice(array_source, i,
upper.indx, lower.indx,
+ upperProvided, lowerProvided,
astate->refattrlength,
astate->refelemlength,
astate->refelembyval,
@@ -456,6 +476,7 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
else
return array_set_slice(array_source, i,
upper.indx, lower.indx,
+ upperProvided, lowerProvided,
sourceData,
eisnull,
astate->refattrlength,
@@ -475,6 +496,7 @@ ExecEvalArrayRef(ArrayRefExprState *astate,
else
return array_get_slice(array_source, i,
upper.indx, lower.indx,
+ upperProvided, lowerProvided,
astate->refattrlength,
astate->refelemlength,
astate->refelembyval,
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ba04b7227ca..4cf14b6f71f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2401,6 +2401,7 @@ _copyAIndices(const A_Indices *from)
{
A_Indices *newnode = makeNode(A_Indices);
+ COPY_SCALAR_FIELD(is_slice);
COPY_NODE_FIELD(lidx);
COPY_NODE_FIELD(uidx);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 356fcafeb49..a13d83181b9 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2151,6 +2151,7 @@ _equalAStar(const A_Star *a, const A_Star *b)
static bool
_equalAIndices(const A_Indices *a, const A_Indices *b)
{
+ COMPARE_SCALAR_FIELD(is_slice);
COMPARE_NODE_FIELD(lidx);
COMPARE_NODE_FIELD(uidx);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 63fae82aba0..fe2c643c348 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2763,6 +2763,7 @@ _outA_Indices(StringInfo str, const A_Indices *node)
{
WRITE_NODE_TYPE("A_INDICES");
+ WRITE_BOOL_FIELD(is_slice);
WRITE_NODE_FIELD(lidx);
WRITE_NODE_FIELD(uidx);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c4bed8a5ef7..223ef175dee 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -434,7 +434,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> columnDef columnOptions
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
- a_expr b_expr c_expr AexprConst indirection_el
+ a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound
columnref in_expr having_clause func_table array_expr
ExclusionWhereClause
%type <list> rowsfrom_item rowsfrom_list opt_col_def_list
@@ -13191,19 +13191,26 @@ indirection_el:
| '[' a_expr ']'
{
A_Indices *ai = makeNode(A_Indices);
+ ai->is_slice = false;
ai->lidx = NULL;
ai->uidx = $2;
$$ = (Node *) ai;
}
- | '[' a_expr ':' a_expr ']'
+ | '[' opt_slice_bound ':' opt_slice_bound ']'
{
A_Indices *ai = makeNode(A_Indices);
+ ai->is_slice = true;
ai->lidx = $2;
ai->uidx = $4;
$$ = (Node *) ai;
}
;
+opt_slice_bound:
+ a_expr { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
indirection:
indirection_el { $$ = list_make1($1); }
| indirection indirection_el { $$ = lappend($1, $2); }
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 4130cbff5ed..591a1f3a681 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -311,18 +311,18 @@ transformArraySubscripts(ParseState *pstate,
elementType = transformArrayType(&arrayType, &arrayTypMod);
/*
- * A list containing only single subscripts refers to a single array
- * element. If any of the items are double subscripts (lower:upper), then
- * the subscript expression means an array slice operation. In this case,
- * we supply a default lower bound of 1 for any items that contain only a
- * single subscript. We have to prescan the indirection list to see if
- * there are any double subscripts.
+ * A list containing only simple subscripts refers to a single array
+ * element. If any of the items are slice specifiers (lower:upper), then
+ * the subscript expression means an array slice operation. In this case,
+ * we convert any non-slice items to slices by treating the single
+ * subscript as the upper bound and supplying an assumed lower bound of 1.
+ * We have to prescan the list to see if there are any slice items.
*/
foreach(idx, indirection)
{
A_Indices *ai = (A_Indices *) lfirst(idx);
- if (ai->lidx != NULL)
+ if (ai->is_slice)
{
isSlice = true;
break;
@@ -356,7 +356,7 @@ transformArraySubscripts(ParseState *pstate,
errmsg("array subscript must have type integer"),
parser_errposition(pstate, exprLocation(ai->lidx))));
}
- else
+ else if (!ai->is_slice)
{
/* Make a constant 1 */
subexpr = (Node *) makeConst(INT4OID,
@@ -367,21 +367,38 @@ transformArraySubscripts(ParseState *pstate,
false,
true); /* pass by value */
}
+ else
+ {
+ /* Slice with omitted lower bound, put NULL into the list */
+ subexpr = NULL;
+ }
lowerIndexpr = lappend(lowerIndexpr, subexpr);
}
- subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- /* If it's not int4 already, try to coerce */
- subexpr = coerce_to_target_type(pstate,
- subexpr, exprType(subexpr),
- INT4OID, -1,
- COERCION_ASSIGNMENT,
- COERCE_IMPLICIT_CAST,
- -1);
- if (subexpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("array subscript must have type integer"),
- parser_errposition(pstate, exprLocation(ai->uidx))));
+ else
+ Assert(ai->lidx == NULL && !ai->is_slice);
+
+ if (ai->uidx)
+ {
+ subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
+ /* If it's not int4 already, try to coerce */
+ subexpr = coerce_to_target_type(pstate,
+ subexpr, exprType(subexpr),
+ INT4OID, -1,
+ COERCION_ASSIGNMENT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+ if (subexpr == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("array subscript must have type integer"),
+ parser_errposition(pstate, exprLocation(ai->uidx))));
+ }
+ else
+ {
+ /* Slice with omitted upper bound, put NULL into the list */
+ Assert(isSlice && ai->is_slice);
+ subexpr = NULL;
+ }
upperIndexpr = lappend(upperIndexpr, subexpr);
}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 1b3fcd629c1..8c2c38dbe67 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -650,7 +650,7 @@ transformAssignmentIndirection(ParseState *pstate,
if (IsA(n, A_Indices))
{
subscripts = lappend(subscripts, n);
- if (((A_Indices *) n)->lidx != NULL)
+ if (((A_Indices *) n)->is_slice)
isSlice = true;
}
else if (IsA(n, A_Star))
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 67c9b357c85..359fb1462bc 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -1995,6 +1995,8 @@ array_get_element_expanded(Datum arraydatum,
* nSubscripts: number of subscripts supplied (must be same for upper/lower)
* upperIndx[]: the upper subscript values
* lowerIndx[]: the lower subscript values
+ * upperProvided[]: true for provided upper subscript values
+ * lowerProvided[]: true for provided lower subscript values
* arraytyplen: pg_type.typlen for the array type
* elmlen: pg_type.typlen for the array's element type
* elmbyval: pg_type.typbyval for the array's element type
@@ -2003,6 +2005,9 @@ array_get_element_expanded(Datum arraydatum,
* Outputs:
* The return value is the new array Datum (it's never NULL)
*
+ * Omitted upper and lower subscript values are replaced by the corresponding
+ * array bound.
+ *
* NOTE: we assume it is OK to scribble on the provided subscript arrays
* lowerIndx[] and upperIndx[]. These are generally just temporaries.
*/
@@ -2011,6 +2016,8 @@ array_get_slice(Datum arraydatum,
int nSubscripts,
int *upperIndx,
int *lowerIndx,
+ bool *upperProvided,
+ bool *lowerProvided,
int arraytyplen,
int elmlen,
bool elmbyval,
@@ -2081,9 +2088,9 @@ array_get_slice(Datum arraydatum,
for (i = 0; i < nSubscripts; i++)
{
- if (lowerIndx[i] < lb[i])
+ if (!lowerProvided[i] || lowerIndx[i] < lb[i])
lowerIndx[i] = lb[i];
- if (upperIndx[i] >= (dim[i] + lb[i]))
+ if (!upperProvided[i] || upperIndx[i] >= (dim[i] + lb[i]))
upperIndx[i] = dim[i] + lb[i] - 1;
if (lowerIndx[i] > upperIndx[i])
return PointerGetDatum(construct_empty_array(elemtype));
@@ -2708,6 +2715,8 @@ array_set_element_expanded(Datum arraydatum,
* nSubscripts: number of subscripts supplied (must be same for upper/lower)
* upperIndx[]: the upper subscript values
* lowerIndx[]: the lower subscript values
+ * upperProvided[]: true for provided upper subscript values
+ * lowerProvided[]: true for provided lower subscript values
* srcArrayDatum: the source for the inserted values
* isNull: indicates whether srcArrayDatum is NULL
* arraytyplen: pg_type.typlen for the array type
@@ -2719,6 +2728,9 @@ array_set_element_expanded(Datum arraydatum,
* A new array is returned, just like the old except for the
* modified range. The original array object is not changed.
*
+ * Omitted upper and lower subscript values are replaced by the corresponding
+ * array bound.
+ *
* For one-dimensional arrays only, we allow the array to be extended
* by assigning to positions outside the existing subscript range; any
* positions between the existing elements and the new ones are set to NULLs.
@@ -2735,6 +2747,8 @@ array_set_slice(Datum arraydatum,
int nSubscripts,
int *upperIndx,
int *lowerIndx,
+ bool *upperProvided,
+ bool *lowerProvided,
Datum srcArrayDatum,
bool isNull,
int arraytyplen,
@@ -2806,6 +2820,13 @@ array_set_slice(Datum arraydatum,
for (i = 0; i < nSubscripts; i++)
{
+ if (!upperProvided[i] || !lowerProvided[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("array slice subscript must provide both boundaries"),
+ errdetail("When assigning to a slice of an empty array value,"
+ " slice boundaries must be fully specified.")));
+
dim[i] = 1 + upperIndx[i] - lowerIndx[i];
lb[i] = lowerIndx[i];
}
@@ -2839,6 +2860,10 @@ array_set_slice(Datum arraydatum,
if (ndim == 1)
{
Assert(nSubscripts == 1);
+ if (!lowerProvided[0])
+ lowerIndx[0] = lb[0];
+ if (!upperProvided[0])
+ upperIndx[0] = dim[0] + lb[0] - 1;
if (lowerIndx[0] > upperIndx[0])
ereport(ERROR,
(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
@@ -2867,6 +2892,10 @@ array_set_slice(Datum arraydatum,
*/
for (i = 0; i < nSubscripts; i++)
{
+ if (!lowerProvided[i])
+ lowerIndx[i] = lb[i];
+ if (!upperProvided[i])
+ upperIndx[i] = dim[i] + lb[i] - 1;
if (lowerIndx[i] > upperIndx[i])
ereport(ERROR,
(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0ab839dc736..280808ae4f9 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9372,10 +9372,12 @@ printSubscripts(ArrayRef *aref, deparse_context *context)
appendStringInfoChar(buf, '[');
if (lowlist_item)
{
+ /* If subexpression is NULL, get_rule_expr prints nothing */
get_rule_expr((Node *) lfirst(lowlist_item), context, false);
appendStringInfoChar(buf, ':');
lowlist_item = lnext(lowlist_item);
}
+ /* If subexpression is NULL, get_rule_expr prints nothing */
get_rule_expr((Node *) lfirst(uplist_item), context, false);
appendStringInfoChar(buf, ']');
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9142e94b070..abd4dd166cc 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -157,9 +157,10 @@ typedef struct Query
List *constraintDeps; /* a list of pg_constraint OIDs that the query
* depends on to be semantically valid */
- List *withCheckOptions; /* a list of WithCheckOption's, which are
- * only added during rewrite and therefore
- * are not written out as part of Query. */
+ List *withCheckOptions; /* a list of WithCheckOption's, which
+ * are only added during rewrite and
+ * therefore are not written out as
+ * part of Query. */
} Query;
@@ -351,13 +352,17 @@ typedef struct A_Star
} A_Star;
/*
- * A_Indices - array subscript or slice bounds ([lidx:uidx] or [uidx])
+ * A_Indices - array subscript or slice bounds ([idx] or [lidx:uidx])
+ *
+ * In slice case, either or both of lidx and uidx can be NULL (omitted).
+ * In non-slice case, uidx holds the single subscript and lidx is always NULL.
*/
typedef struct A_Indices
{
NodeTag type;
- Node *lidx; /* NULL if it's a single subscript */
- Node *uidx;
+ bool is_slice; /* true if slice (i.e., colon present) */
+ Node *lidx; /* slice lower bound, if any */
+ Node *uidx; /* subscript, or slice upper bound if any */
} A_Indices;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 60c1ca2c8dc..4dbcc10e337 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -341,6 +341,9 @@ typedef struct WindowFunc
* reflowerindexpr must be the same length as refupperindexpr when it
* is not NIL.
*
+ * In the slice case, individual expressions in the subscript lists can be
+ * NULL, meaning "substitute the array's current lower or upper bound".
+ *
* Note: the result datatype is the element type when fetching a single
* element; but it is the array type when doing subarray fetch or either
* type of store.
@@ -360,7 +363,7 @@ typedef struct ArrayRef
List *refupperindexpr;/* expressions that evaluate to upper array
* indexes */
List *reflowerindexpr;/* expressions that evaluate to lower array
- * indexes */
+ * indexes, or NIL for single array element */
Expr *refexpr; /* the expression that evaluates to an array
* value */
Expr *refassgnexpr; /* expression for the source value, or NULL if
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index c25b80d272a..716e75637b0 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -377,9 +377,11 @@ extern Datum array_set_element(Datum arraydatum, int nSubscripts, int *indx,
int arraytyplen, int elmlen, bool elmbyval, char elmalign);
extern Datum array_get_slice(Datum arraydatum, int nSubscripts,
int *upperIndx, int *lowerIndx,
+ bool *upperProvided, bool *lowerProvided,
int arraytyplen, int elmlen, bool elmbyval, char elmalign);
extern Datum array_set_slice(Datum arraydatum, int nSubscripts,
int *upperIndx, int *lowerIndx,
+ bool *upperProvided, bool *lowerProvided,
Datum srcArrayDatum, bool isNull,
int arraytyplen, int elmlen, bool elmbyval, char elmalign);
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 73fb5a248b4..baccca14afd 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -125,6 +125,16 @@ SELECT a[1:3],
{16,25,23} | {} | {foobar,new_word} | {{elt2}}
(3 rows)
+SELECT b[1:1][2][2],
+ d[1:1][2]
+ FROM arrtest;
+ b | d
+-----------------------+---------------
+ {{{113,142},{1,147}}} | {}
+ {} | {}
+ {} | {{elt1,elt2}}
+(3 rows)
+
INSERT INTO arrtest(a) VALUES('{1,null,3}');
SELECT a FROM arrtest;
a
@@ -152,6 +162,107 @@ SELECT a,b,c FROM arrtest;
[4:4]={NULL} | {3,4} | {foo,new_word}
(3 rows)
+-- test mixed slice/scalar subscripting
+select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
+ int4
+---------------------------
+ {{1,2,3},{4,5,6},{7,8,9}}
+(1 row)
+
+select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
+ int4
+---------------
+ {{1,2},{4,5}}
+(1 row)
+
+select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[];
+ int4
+--------------------------------------
+ [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
+(1 row)
+
+select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
+ int4
+---------------
+ {{5,6},{8,9}}
+(1 row)
+
+-- test slices with empty lower and/or upper index
+CREATE TEMP TABLE arrtest_s (
+ a int2[],
+ b int2[][]
+);
+INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}');
+INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}');
+SELECT * FROM arrtest_s;
+ a | b
+-------------------+--------------------------------------
+ {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
+ [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
+(2 rows)
+
+SELECT a[:3], b[:2][:2] FROM arrtest_s;
+ a | b
+-----------+---------------------------
+ {1,2,3} | {{1,2},{4,5}}
+ {1,2,3,4} | {{1,2,3},{4,5,6},{7,8,9}}
+(2 rows)
+
+SELECT a[2:], b[2:][2:] FROM arrtest_s;
+ a | b
+-----------+---------------
+ {2,3,4,5} | {{5,6},{8,9}}
+ {3,4,5} | {{9}}
+(2 rows)
+
+SELECT a[:], b[:] FROM arrtest_s;
+ a | b
+-------------+---------------------------
+ {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
+ {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
+(2 rows)
+
+-- updates
+UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}'
+ WHERE array_lower(a,1) = 1;
+SELECT * FROM arrtest_s;
+ a | b
+-------------------+--------------------------------------
+ [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
+ {11,12,13,4,5} | {{11,12,3},{14,15,6},{7,8,9}}
+(2 rows)
+
+UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}';
+SELECT * FROM arrtest_s;
+ a | b
+---------------------+---------------------------------------
+ [0:4]={1,2,3,23,24} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}}
+ {11,12,23,24,25} | {{11,12,3},{14,25,26},{7,28,29}}
+(2 rows)
+
+UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}';
+SELECT * FROM arrtest_s;
+ a | b
+------------------------+---------------------------------------
+ [0:4]={11,12,13,14,15} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}}
+ {11,12,13,14,15} | {{11,12,3},{14,25,26},{7,28,29}}
+(2 rows)
+
+UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small
+ERROR: source array too small
+INSERT INTO arrtest_s VALUES(NULL, NULL);
+UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null
+ERROR: array slice subscript must provide both boundaries
+DETAIL: When assigning to a slice of an empty array value, slice boundaries must be fully specified.
+-- check with fixed-length-array type, such as point
+SELECT f1[0:1] FROM POINT_TBL;
+ERROR: slices of fixed-length arrays not implemented
+SELECT f1[0:] FROM POINT_TBL;
+ERROR: slices of fixed-length arrays not implemented
+SELECT f1[:1] FROM POINT_TBL;
+ERROR: slices of fixed-length arrays not implemented
+SELECT f1[:] FROM POINT_TBL;
+ERROR: slices of fixed-length arrays not implemented
--
-- test array extension
--
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index b1dd6514405..a2c3db11274 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -86,6 +86,10 @@ SELECT a[1:3],
d[1:1][2:2]
FROM arrtest;
+SELECT b[1:1][2][2],
+ d[1:1][2]
+ FROM arrtest;
+
INSERT INTO arrtest(a) VALUES('{1,null,3}');
SELECT a FROM arrtest;
UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL;
@@ -93,6 +97,43 @@ SELECT a FROM arrtest WHERE a[2] IS NULL;
DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL;
SELECT a,b,c FROM arrtest;
+-- test mixed slice/scalar subscripting
+select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
+select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
+select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[];
+select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
+
+-- test slices with empty lower and/or upper index
+CREATE TEMP TABLE arrtest_s (
+ a int2[],
+ b int2[][]
+);
+INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}');
+INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}');
+
+SELECT * FROM arrtest_s;
+SELECT a[:3], b[:2][:2] FROM arrtest_s;
+SELECT a[2:], b[2:][2:] FROM arrtest_s;
+SELECT a[:], b[:] FROM arrtest_s;
+
+-- updates
+UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}'
+ WHERE array_lower(a,1) = 1;
+SELECT * FROM arrtest_s;
+UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}';
+SELECT * FROM arrtest_s;
+UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}';
+SELECT * FROM arrtest_s;
+UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small
+INSERT INTO arrtest_s VALUES(NULL, NULL);
+UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null
+
+-- check with fixed-length-array type, such as point
+SELECT f1[0:1] FROM POINT_TBL;
+SELECT f1[0:] FROM POINT_TBL;
+SELECT f1[:1] FROM POINT_TBL;
+SELECT f1[:] FROM POINT_TBL;
+
--
-- test array extension
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 15:11:16 +0000