PostgreSQL函数处理枚举类型JSON数组时触发无效令牌错误

问题根源 #

传递自定义枚举my_category的数组参数时，枚举值以无引号的纯文本形式（比如type5）存在，而JSON语法要求字符串必须被双引号包裹，直接转换为JSONB时就会触发invalid input syntax for type json错误。整数类型本身符合JSON的数字语法规则，所以不会出现这类问题。

解决方案 #

1. 构造参数时主动给枚举值加引号 #

调用函数传递JSON参数时，确保枚举值是带双引号的字符串数组，示例格式：

{"categories": ["type1", "type5", "type3"]}

避免使用无引号的纯文本写法：

{"categories": [type1, type5, type3]}

2. 在函数内部处理枚举值的转换 #

如果无法修改参数传入形式，可以在函数里先把枚举值转为带引号的字符串，再拼接成合法的JSON数组后转JSONB。示例函数修改如下：

CREATE OR REPLACE FUNCTION select_ids(query_params JSON)
RETURNS SETOF INTEGER AS $$
DECLARE
    category_array JSONB;
BEGIN
    -- 处理枚举参数：提取枚举值转为字符串并添加引号，拼接为合法JSON数组
    IF query_params ? 'categories' THEN
        category_array := ('["' || array_to_string(array(
            SELECT unnest((query_params->>'categories')::TEXT::my_category[])::TEXT
        ), '", "') || '"]')::JSONB;
    ELSE
        category_array := '[]'::JSONB;
    END IF;

    -- 执行筛选逻辑
    RETURN QUERY
    SELECT id FROM features
    WHERE (category_array = '[]'::JSONB OR category = ANY(category_array::my_category[]));
END;
$$ LANGUAGE plpgsql;

3. 用to_json函数自动序列化枚举值 #

利用PostgreSQL的to_json函数，它会自动将枚举值序列化为符合JSON规范的带引号字符串，写法更简洁：

CREATE OR REPLACE FUNCTION select_ids(query_params JSON)
RETURNS SETOF INTEGER AS $$
BEGIN
    RETURN QUERY
    SELECT id FROM features
    WHERE (
        query_params->>'categories' IS NULL
        OR category = ANY(
            ARRAY(SELECT jsonb_array_elements_text(to_json((query_params->>'categories')::my_category[])::JSONB))::my_category[]
        )
    );
END;
$$ LANGUAGE plpgsql;

关键说明 #

• JSON语法中，字符串必须用双引号包裹，无引号的纯文本标识符不符合规范，只有数字、布尔值、null可以不用引号。
• PostgreSQL的自定义枚举类型以纯文本形式存储，但转换为JSON时必须显式处理为带引号的字符串，否则会触发语法错误。

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