在文本数组上应用`trim()`和`regexp_replace()` [英] Apply `trim()` and `regexp_replace()` on text array

问题描述

我正在尝试将PostgreSQL文本数组转换为一个数组，在该数组中，每个值都被替换为一个值，其中，每个空格的每一边都被字符包围.换句话说，我正在尝试对文本数组中的每个值应用 trim()和 regexp_replace().这是在数据库函数内部完成的.

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;

不幸的是，当我使用 multiplevalues ='{"red"，"blue"，"Ye llow"}"作为参数之一调用函数时，返回值是完全相同的文本数组.如何获得'{"red"，"blue"，"yellow"}'作为返回值?

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?

一段时间以来，我一直在盯着 trim()， regexp_replace()和 FOREACH 循环的定义，我可能只需要其他人的支票即可.

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.

推荐答案

您的代码永远不会更改 multiplevalues 数组.它只是更改每个元素，然后丢弃该新值.

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;

---

但是可以使用unnest和array_agg以及简单的SQL函数(而不是PL/pgSQL)来完成此操作

---

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;

这篇关于在文本数组上应用`trim()`和`regexp_replace()`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！