英文:
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: 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)
数据类型。它是匹配的,但不符合这个错误信息的要求。
我完全迷失了,所以非常感谢任何帮助。
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论