Postgres array_replace() function to replace sub array

Question

The postgres function array_replace(anyarray, anyelement, anyelement) is useful when you need to replace one single element in the array.

But I need to replace a sequence of elements in the array with another sequence. I'd like to be able to have a function like this: array_replace(anyarray, anyarray, anyarray)

For example. If I have this array: [A, B, C, D, E, F]

I'd like to change it to [A, 1, 2, 3, D, E, F]

By calling a function like this: array_replace([A, B, C, D, E, F], [B, C], [1, 2, 3]);

The elements to be replaced should all be in the exact order in which are passed to the function, one following the other. So, the following call do not alter the original array, because B and C are not near elements of the original array:

array_replace([A, B, X, C, D, E, F], [B, C], [1, 2, 3]);

How do I create such a function ?

Evan W. · Accepted Answer · 2018-07-25 23:00:01Z

2

It sounds like what you want is a regexp_replace over an array. Postgres allows arrays to be converted to strings (and vice-versa). However, the trick is to treat each array element as a word and match on the equivalent of word boundaries.

The following code returns {A,1,2,3,D,E,F}

Select array(
    select regexp_replace(
          (select array_to_string(array['A','B','C','D','E','F'],', ')),
          (select ' ' || array_to_string(array['B','C']),', ')),
          (select array_to_string(array[1,2,3],',')),
          'g'))))

(I'm sure there's a way to do this with positional indexing, and creating a function, etc., but this my quick solution).

edited Jul 25, 2018 at 23:00

answered Jun 13, 2018 at 19:23

Evan W.

3422 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nicola Lepetit Over a year ago

This is a nice solution, but since you are dealing with strings, it could happens that you replace partial strings and not the entire value of an array element. This function does not works for input like: array_replace([1, 12, 3, 4, 5, 6], [2, 3], [17, 18, 19]);. The secon element will become 117.

Evan W. Over a year ago

Great point; I've edited it to modify how the strings are spaced... the updated regex now attempts to respect word boundaries. I've tested it with your example, the second element does not become 117.

klin · Accepted Answer · 2018-06-15 01:29:50Z

The function may look like this:

create or replace function array_replace_array(anyarray, anyarray, anyarray)
returns anyarray language plpgsql as $$
declare
    l1 int = array_length($1, 1);
    l2 int = array_length($2, 1);
    i int;
begin
    for i in 1 .. l1- l2+ 1 loop
        if $1[i : i+ l2- 1] = $2 then
            return $1[1 : i- 1] || $3 || $1[i+ l2 : l1];
        end if;
    end loop;
    return $1;
end $$;

Examples:

select 
    array_replace_array('{A,B,C,D,E}'::text[], '{B,C}', '{X,Y,Z}') as "AXYZDE",
    array_replace_array('{A,B,C,D,E}'::text[], '{B,B}', '{X,Y,Z}') as "ABCDE",
    array_replace_array('{A,B,C,D,E}'::text[], '{A,B,C,D,E}', '{X,Y,Z}') as "XYZ",
    array_replace_array('{1,2,3,4}'::int[], '{2,3}', '{3,2}') as "1324";

    AXYZDE     |    ABCDE    |   XYZ   |   1324    
---------------+-------------+---------+-----------
 {A,X,Y,Z,D,E} | {A,B,C,D,E} | {X,Y,Z} | {1,3,2,4}
(1 row)

Collectives™ on Stack Overflow

Postgres array_replace() function to replace sub array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related