英文:
Tried calling a user-defined function but got an error
问题
我尝试创建一个用户定义函数（在PostgreSQL中），允许您将一些字符串从小写转换为大写。我正在使用一个虚拟实验室环境中提供给我的学校的“DVD租赁业务”示例数据库中的数据集。
以下是代码：
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();
我收到了一个错误消息 pgadmin4：
ERROR: 结果查询的结构与函数结果类型不匹配
DETAIL: 返回的类型text与第2列中期望的类型character varying不匹配。
CONTEXT: PL/pgSQL function get_uppercase_film_names() line 3 at RETURN QUERY
SQL 状态: 42804
这很奇怪，因为当我对film表执行选择查询时，title 明显是 character varying(255) 数据类型。它确实匹配，但不符合这个错误消息的要求。
我完全不知所措，所以任何帮助将不胜感激。
我尝试将返回查询强制转换为：
SELECT film.film_id, UPPER(film.title::VARCHAR(255)) AS uppercase_name
但在尝试调用函数后，我收到了相同的错误消息。
英文:
I tried creating a user-defined function (in postgreSQL) that allows you to transform some strings from lower to uppercase. The dataset I'm using is from a 'DVD rental business' sample database in a virtual lab environment my school is providing me.
Here's the code:
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;
When I run this, it is successful.
When I go to call the function:
SELECT film_id, uppercase_name FROM get_uppercase_film_names();
I get an error message pgadmin4:
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
This is strange because when I do a select query on the film table, the title is definitely a character varying(255) datatype. It does match but not according to this error message.
I'm totally lost so any help will be much appreciated.
I did try casting the return query with:
SELECT film.film_id, UPPER(film.title::VARCHAR(255)) AS uppercase_name
but I got the same error message after trying to call the function.
答案1
得分: 0
尝试将您的 CHARACTER VARYING(255) 修改为 TEXT，通常这是一个安全的选择，并且应该有助于避免您遇到的任何不匹配问题。
CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name TEXT)
AS
$$
BEGIN
RETURN QUERY
SELECT film.film_id, UPPER(film.title) AS uppercase_name
FROM film;
END;
$$
LANGUAGE plpgsql;
英文:
Try modifying your CHARACTER VARYING(255) to TEXT as it is generally a safe choice, and should help avoid any mismatch issues you’re having.
CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name TEXT)
AS
$$
BEGIN
RETURN QUERY
SELECT film.film_id, UPPER(film.title) AS uppercase_name
FROM film;
END;
$$
LANGUAGE plpgsql;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论