Apply `trim()` and `regexp_replace()` on text array

Question

I am trying to transform a PostgreSQL text array into one where each value is replaced by one where each white space is surrounded by a character on each side. In other words, I am trying to apply trim() and regexp_replace() on each value in a text array. This is all done (among other things) inside a database function.

    CREATE OR REPLACE FUNCTION manipulate_array(multiplevalues text[])
    RETURNS text[] AS 
    $BODY$
        DECLARE 
            singlevalue text;

        BEGIN
            FOREACH singlevalue IN ARRAY multiplevalues LOOP
                SELECT trim(regexp_replace(singlevalue, '\s+', ' ', 'g')) INTO singlevalue;
            END LOOP;

            RETURN multiplevalues;
        END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION manipulate_array(multiplevalues text[]) OWNER TO username;

Unfortunately, when I call the function with multiplevalues = '{" red ", " blue ", " Ye llow "}' as one of the arguments the returned value is the exact same text array. How do I get '{"red", "blue", "yellow"}' as a return value?

I have been staring at the definitions for trim(), regexp_replace() and the FOREACH loop for a while now, and I might just need a check from someone else.

Answer 1

Your code never changes the multiplevalues array. It just changes each element, then throws that new value away.

You need a variable where you can aggregate your results into:

CREATE OR REPLACE FUNCTION manipulate_array(multiplevalues text[])
RETURNS text[] AS 
$BODY$
  DECLARE 
    singlevalue text;
    l_result text[] := '{}'::text[]; -- initialize with an empty array
  BEGIN
    FOREACH singlevalue IN ARRAY multiplevalues LOOP
        SELECT trim(regexp_replace(singlevalue, '\s+', ' ', 'g')) INTO singlevalue;
        l_result := l_result || singlevalue; -- append to the result
    END LOOP;

    RETURN l_result; -- return the new array, not the old one
  END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

---

But this can be done a bit simpler using a unnest and array_agg and a plain SQL function (rather then PL/pgSQL)

You need to first unnest the array, trim the values and the aggregate that back into an array.

I am not sure I understand what you are trying to do, but this will trim all values inside the array and return a new one:

create function trim_all(p_values text[])
  returns text[]
as
$$
  select array_agg(trim(regexp_replace(t.v, '\s+', ' ', 'g')) order by t.nr)
    from unnest(p_values) with ordinality as t(v, nr);
$$
language sql;