array_replace() and setting an array value explicitly
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
Each of the approaches described in this section, using the array_replace()
function and setting an addressed array value explicitly and in place, can be used to change values in an array. But the two approaches differ importantly:
-
array_replace()
changes all values that match the specified value to the same new value, insensitively to their address in the array. -
Setting an addressed array value changes that one value insensitively to its present value.
array_replace()
Purpose: Return a new array that is derived from the input array by replacing every array value that is equal to the specified value with the specified new value.
Signature
input value: anyarray, anyelement, anyelement
return value: anyarray
Example:
create type rt as (f1 int, f2 text);
create table t(k int primary key, arr rt[]);
insert into t(k, arr)
values (1, '{"(1,rabbit)","(2,hare)","(3,squirrel)","(4,horse)"}'::rt[]);
select arr as "old value of arr" from t where k = 1;
update t
set arr = array_replace(arr, '(3,squirrel)', '(3,bobcat)')
where k = 1;
select arr as "new value of arr" from t where k = 1;
This is the result of the two queries:
old value of arr
------------------------------------------------------
{"(1,rabbit)","(2,hare)","(3,squirrel)","(4,horse)"}
new value of arr
----------------------------------------------------
{"(1,rabbit)","(2,hare)","(3,bobcat)","(4,horse)"}
Semantics:
One-dimensional array of primitive scalar values.
do $body$
declare
old_val constant int := 42;
new_val constant int := 17;
arr constant int[] :=
array[1, old_val, 3, 4, 5, old_val, 6, 7];
expected_modified_arr constant int[] :=
array[1, new_val, 3, 4, 5, new_val, 6, 7];
begin
assert
array_replace(arr, old_val, new_val) = expected_modified_arr,
'unexpected';
end;
$body$;
One-dimensional array of "row" type values.
The definition of "rt" used here is the same as the example above used. Don't create again if it already exists.
create type rt as (f1 int, f2 text);
do $body$
declare
old_val constant rt := (42, 'x');
new_val constant rt := (17, 'y');
arr constant rt[] :=
array[(1, 'a')::rt, old_val, (1, 'a')::rt, (2, 'b')::rt, (3, 'c')::rt,
old_val, (4, 'd')::rt, (5, 'e')::rt];
expected_modified_arr constant rt[] :=
array[(1, 'a')::rt, new_val, (1, 'a')::rt, (2, 'b')::rt, (3, 'c')::rt,
new_val, (4, 'd')::rt, (5, 'e')::rt];
begin
assert
array_replace(arr, old_val, new_val) = expected_modified_arr,
'unexpected';
end;
$body$;
Two-dimensional array of primitive scalar values. This is sufficient to illustrate the semantics of the general multidimensional case. The function's signature (at the start of this section) shows that the to-be-replaced value and the replacement value are instances of anyelement
. There is no overload where these two parameters accept instances of anyarray
. This restriction is understood by picturing the internal representation as a linear ribbon of values, as was explained in Synopsis. The replacement works by scanning along the ribbon, finding each occurrence in turn of the to-be-replaced value, and replacing it.
Here is a postive illustration:
do $body$
declare
old_val constant int := 22;
new_val constant int := 97;
arr int[] :=
array[
array[11, 12],
array[11, old_val],
array[32, 33]
];
expected_modified_arr constant int[] :=
array[
array[11, 12],
array[11, new_val],
array[32, 33]
];
begin
arr := array_replace(arr, old_val, new_val);
assert
arr = expected_modified_arr,
'unexpected';
end;
$body$;
And here is a negative illustration:
do $body$
declare
old_val constant int[] := array[22, 23];
new_val constant int[] := array[87, 97];
arr int[] :=
array[
array[11, 12],
old_val,
array[32, 33]
];
expected_modified_arr constant int[] :=
array[
array[11, 12],
new_val,
array[32, 33]
];
begin
begin
-- Causes: 42883: function array_replace(integer[], integer[], integer[]) does not exist.
arr := array_replace(arr, old_val, new_val);
exception
when undefined_function then null;
end;
-- The goal is met by replacing the scalar values one by one.
arr := array_replace(arr, old_val[1], new_val[1]);
arr := array_replace(arr, old_val[2], new_val[2]);
assert
arr = expected_modified_arr,
'unexpected';
end;
$body$;
Setting an array value explicitly and in place
Purpose: Change an array in place by changing an explicitly addressed value.
Signature
-- Uses the notation
-- arr[idx_1][idx_2]...[idx_N]
-- for an N-dimensional array.
input/output value: anyarray, "vector of index values"
Example:
create table t(k int primary key, arr int[]);
insert into t(k, arr) values (1,
'{1, 2, 3, 4}');
update t set arr[2] = 42 where k = 1;
select arr from t where k = 1;
This is the result:
arr
------------
{1,42,3,4}
Semantics:
Array of primitive scalar values. Notice that the starting value is "snapshotted" as old_arr
and that this is marked constant
. Notice too that "expected_modified_arr" is marked constant
. This proves that the modification was done in place within the only array value that is not marked constant
.
do $body$
declare
old_val constant int := 42;
new_val constant int := 17;
arr int[] := array[1, 2, old_val, 4];
expected_modified_arr constant int[] := array[1, 2, new_val, 4];
old_arr constant int[] := arr;
begin
arr[3] := new_val;
assert
old_arr = '{1, 2, 42, 4}' and
expected_modified_arr = '{1, 2, 17, 4}' and
arr = expected_modified_arr,
'unexpected';
end;
$body$;
Array of "record" type values.
The definition of "rt" used here is the same as the example above used. Don't create again if it already exists.
create type rt as (f1 int, f2 text);
do $body$
declare
old_val constant rt := (42, 'x');
new_val constant rt := (17, 'y');
arr rt[] :=
array[(1, 'a')::rt, old_val, (1, 'a')::rt, (2, 'b')::rt, (3, 'c')::rt,
old_val, (4, 'd')::rt, (5, 'e')::rt];
expected_modified_arr constant rt[] :=
array[(1, 'a')::rt, new_val, (1, 'a')::rt, (2, 'b')::rt, (3, 'c')::rt,
new_val, (4, 'd')::rt, (5, 'e')::rt];
old_arr constant rt[] := arr;
begin
arr[2] := new_val;
arr[6] := new_val;
assert
old_arr =
'{"(1,a)","(42,x)","(1,a)","(2,b)","(3,c)","(42,x)","(4,d)","(5,e)"}' and
expected_modified_arr =
'{"(1,a)","(17,y)","(1,a)","(2,b)","(3,c)","(17,y)","(4,d)","(5,e)"}' and
arr = expected_modified_arr,
'unexpected';
end;
$body$;
Two-dimensional array of primitive scalar values. This is sufficient to illustrate the semantics of the general multidimensional case. The approach is just the same as when array_replace()
is used to meet the same goal. You have no choice but to target the values explicitly.
do $body$
declare
old_val constant int[] := array[21, 22, 23, 24];
new_val constant int[] := array[81, 82, 83, 84];
arr int[] :=
array[
array[11, 12, 13, 14],
old_val,
array[31, 32, 33, 34]
];
expected_modified_arr constant int[] :=
array[
array[11, 12, 13, 14],
new_val,
array[31, 32, 33, 34]
];
len_1 constant int := array_length(arr, 1);
len_2 constant int := array_length(arr, 2);
begin
assert
(len_1 = 3) and (len_2 = 4),
'unexpected';
-- OK to extract a slice. But, even though it's tempting to picture this as one row,
-- it is nevertheless a 2-d array with "array_length(arr, 1)" equal to 1.
assert
arr[2:2][1:4] = array[old_val],
'unexpected';
-- You cannot use the slice notation to specify the target of an assignment.
-- So this
-- arr[2:2][1:4] = array[new_val];
-- causes a compilation error.
-- Similarly, this is meaningless. (But it doesn't cause a compilation error.)
-- Because it's a 2-d array, its values (individual values or slices) must be
-- addressed using two indexes or two slice ranges.
assert
arr[2] is null,
'unexpected';
-- Change the individual, addressable, values one by one.
for j in array_lower(arr, 2)..array_upper(arr, 2) loop
arr[2][j] := new_val[j];
end loop;
assert
arr = expected_modified_arr,
'unexpected';
end;
$body$;