PostgreSQL自定义函数调用报错：返回类型不匹配问题求助

问题场景 #

基于DVD租赁业务样例数据库创建PL/pgSQL函数，用于返回电影ID及转成大写的电影名。函数创建成功，但调用时返回类型不匹配错误。

原函数代码 #

CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS

$$
BEGIN
     RETURN QUERY 
     SELECT film.film_id, UPPER(film.title) AS uppercase_name
     FROM film;
END;
$$
LANGUAGE plpgsql;

调用语句 #

SELECT film_id, uppercase_name FROM get_uppercase_film_names();

报错信息 #

ERROR: structure of query does not match function result type
DETAIL: Returned type text does not match expected type character varying in column 2.
CONTEXT: PL/pgSQL function get_uppercase_film_names() line 3 at RETURN QUERY
SQL state: 42804

已确认film.title字段为character varying(255)类型，尝试将title先转换为VARCHAR(255)再传入UPPER()，仍报相同错误。

解决方案 #

原因分析 #

PostgreSQL的UPPER()函数返回值固定为text类型，无论输入参数是varchar还是text。因此即使先转换输入参数的类型，UPPER()的输出依然是text，与函数定义的character varying(255)返回类型不匹配。

修改后的函数代码 #

需对UPPER()的返回结果进行类型转换，确保与函数定义的返回类型一致：

CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS
$$
BEGIN
     RETURN QUERY 
     SELECT film.film_id, UPPER(film.title)::CHARACTER VARYING(255) AS uppercase_name
     FROM film;
END;
$$
LANGUAGE plpgsql;

简化方案（使用SQL函数） #

如果不需要PL/pgSQL的流程控制逻辑，可直接使用SQL函数实现，代码更简洁：

CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS
$$
     SELECT film.film_id, UPPER(film.title)::CHARACTER VARYING(255) AS uppercase_name
     FROM film;
$$
LANGUAGE sql;

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