尝试调用一个用户定义的函数,但出现了错误。

huangapple go评论74阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年8月9日 14:36:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76865161.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定