Postgres中regexp_matches返回值类型及转数组方案问询

问题根因 #

1. 变量类型声明不匹配：regexp_matches的单条返回值本身确实是text[]数组类型，但你声明的接收变量one_match是text文本类型，PL/pgSQL执行赋值时会自动将数组隐式转换为{元素}格式的文本字符串，这就是你看到结果带花括号但无法按下标访问的核心原因。
2. PostgreSQL数组下标规则：PostgreSQL的数组下标默认从1开始，即使变量类型正确，你尝试用[0]访问也会得到空值。

无需临时处理的实现方案 #

不需要做trim花括号的额外处理，有两种常用实现方式：

方案1：最简单SQL实现（无需循环）

直接用array_agg聚合regexp_matches的返回结果即可，函数可以大幅简化：

CREATE OR REPLACE FUNCTION lower_words_arr(input_str text, match_expr text) RETURNS text[] AS $$
BEGIN
    RETURN (
        SELECT array_agg(matches[1]) 
        FROM regexp_matches(lower(input_str), match_expr, 'g') AS t(matches)
    );
END;
$$ LANGUAGE plpgsql STABLE;

方案2：保留循环逻辑的修正写法

如果需要保留原来的循环写法，仅修改接收变量的类型即可：

CREATE OR REPLACE FUNCTION lower_words_arr(input_str text, match_expr text) RETURNS text[] AS $$
DECLARE
    words_arr text[];
    one_match text[]; -- 将类型从text改为text[]
BEGIN
    FOR one_match IN SELECT regexp_matches(lower(input_str), match_expr, 'g') AS match 
    LOOP
        -- 可正常按下标访问，注意下标从1开始
        RAISE NOTICE 'match = %', one_match[1];
        words_arr := array_append(words_arr, one_match[1]);
    END LOOP;

    RETURN words_arr;
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者smile2day