PostgreSQL中将字符串转换为复合类型数组的问题

首先得明确你遇到问题的根源：你的输入文本里，每个t_attr记录的id和data字段本身就包含逗号，但PostgreSQL默认的记录/数组解析逻辑会把逗号当成字段分隔符，所以直接用array[txt]::t_attr[]转换时，它会错误地把"id1,id2,id3"拆成多个字段，导致"Too many columns"或者格式错误的提示。

下面给你两种可行的解决方案，按需选择：

方案一：通过字符串预处理实现类型转换 #

先把输入文本转换成PostgreSQL能正确识别的复合数组格式，再进行类型转换：

WITH input AS (
    SELECT '("id1,id2,id3", "dat1,data2,dat3"), ("id4,id5,id6", "dat4,dat5,dat6")' AS txt
),
cleaned_records AS (
    -- 1. 去掉字符串首尾的括号；2. 按"), ("拆分出单个记录字符串；3. 把双引号替换为单引号
    SELECT 
        regexp_replace(unnest(string_to_array(regexp_replace(txt, '^\(|)$', '', 'g'), '), ('))), '"', '''', 'g') AS rec_str
    FROM input
)
-- 将每个处理后的记录字符串转为t_attr，再聚合为数组
SELECT array_agg(rec_str::t_attr) AS arr_attr
FROM cleaned_records;

执行后你就能得到预期的数组：

• arr_attr[1]的值为('id1,id2,id3', 'dat1,data2,dat3')
• arr_attr[2]的值为('id4,id5,id6', 'dat4,dat5,dat6')

方案二：用自定义函数解析输入（更灵活） #

如果你的输入格式可能有变化，写一个PL/pgSQL函数来手动解析会更可靠：

-- 创建自定义解析函数
CREATE OR REPLACE FUNCTION parse_attr_array(txt text) RETURNS t_attr[] AS $$
DECLARE
    result_arr t_attr[];
    individual_records text[];
    single_record text;
    field_matches text[];
BEGIN
    -- 移除输入字符串首尾的括号（如果存在）
    txt := regexp_replace(txt, '^\(|)$', '', 'g');
    -- 拆分出每个独立的t_attr记录（避开字段内的逗号）
    individual_records := regexp_split_to_array(txt, '\), \(');
    
    -- 遍历每个记录，提取id和data字段
    FOREACH single_record IN ARRAY individual_records LOOP
        -- 用正则匹配被双引号包裹的两个字段
        field_matches := regexp_match(single_record, '^"([^"]+)", "([^"]+)"$');
        IF field_matches IS NOT NULL THEN
            -- 将字段组合成t_attr并加入结果数组
            result_arr := result_arr || (field_matches[1], field_matches[2])::t_attr;
        END IF;
    END LOOP;
    
    RETURN result_arr;
END;
$$ LANGUAGE plpgsql;

调用函数非常简单：

SELECT parse_attr_array('("id1,id2,id3", "dat1,data2,dat3"), ("id4,id5,id6", "dat4,dat5,dat6")') AS arr_attr;

你可以用下面的语句验证结果是否符合预期：

SELECT 
    (arr_attr[1]).id AS first_id,
    (arr_attr[1]).data AS first_data,
    (arr_attr[2]).id AS second_id,
    (arr_attr[2]).data AS second_data
FROM (
    SELECT parse_attr_array('("id1,id2,id3", "dat1,data2,dat3"), ("id4,id5,id6", "dat4,dat5,dat6")') AS arr_attr
) t;

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