PostgreSQL函数返回类型不匹配：text与character varying冲突

错误原因 #

PostgreSQL里字符串字面量（比如'f>17'）默认是text类型，而你定义的函数返回表中gender_age_category字段是varchar(255)类型。虽然text和varchar在多数场景下兼容，但函数返回结果要求类型严格匹配，因此触发了类型不匹配的报错。单独执行SELECT语句时PostgreSQL会自动隐式转换，所以不会出问题，但函数场景下不允许这种隐式转换。

修复方案 #

显式将CASE表达式的结果转换为varchar(255)类型，有两种常用方式：

方式1：用类型转换操作符:: #

修改CASE部分代码：

CASE
    WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'::varchar(255)
    WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'::varchar(255)
    WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'::varchar(255)
    WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18'::varchar(255)
END AS gender_age_category

方式2：用CAST函数 #

CAST(
    CASE
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18'
    END AS varchar(255)
) AS gender_age_category

完整修复后的函数代码 #

CREATE OR REPLACE FUNCTION get_gender_age_log_counts(start_date character varying(255), end_date character varying(255))
RETURNS TABLE (
    gender_age_category varchar(255),
    facility_id varchar(255),
    total bigint,
    general bigint,
    course bigint,
    club bigint
) AS $$
BEGIN
RETURN QUERY
SELECT
    CASE
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'::varchar(255)
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'::varchar(255)
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'::varchar(255)
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18'::varchar(255)
    END AS gender_age_category,
    l.facility_id::varchar(255), -- 额外建议：如果原表facility_id是text类型，也统一转换避免后续问题
    COALESCE(SUM(CASE WHEN l.card_id = m.card_id THEN 1 ELSE 0 END),0) AS total,
    COALESCE(SUM(CASE WHEN l.log_type = 'general' THEN 1 ELSE 0 END), 0) AS general,
    COALESCE(SUM(CASE WHEN l.log_type = 'course' THEN 1 ELSE 0 END), 0) AS course,
    COALESCE(SUM(CASE WHEN l.log_type = 'club' THEN 1 ELSE 0 END), 0) AS club
FROM members m
LEFT JOIN logs l ON m.card_id = l.card_id
WHERE l."login" >= start_date::date AND l."login" <= end_date::date
GROUP BY gender_age_category, l.facility_id
ORDER BY gender_age_category ASC;
END;
$$ LANGUAGE plpgsql;

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