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

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

Tried calling a user-defined function but got an error

问题

我尝试创建一个用户定义函数(在PostgreSQL中),允许您将一些字符串从小写转换为大写。我正在使用一个虚拟实验室环境中提供给我的学校的“DVD租赁业务”示例数据库中的数据集。

以下是代码:

  1. CREATE OR REPLACE FUNCTION get_uppercase_film_names()
  2. RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS
  3. $$
  4. BEGIN
  5. RETURN QUERY
  6. SELECT film.film_id, UPPER(film.title) AS uppercase_name
  7. FROM film;
  8. END;
  9. $$
  10. LANGUAGE plpgsql;

当我运行这个代码时,它是成功的。

当我尝试调用这个函数时:

  1. SELECT film_id, uppercase_name FROM get_uppercase_film_names();

我收到了一个错误消息 pgadmin4:

  1. ERROR: 结果查询的结构与函数结果类型不匹配
  2. DETAIL: 返回的类型text与第2列中期望的类型character varying不匹配
  3. CONTEXT: PL/pgSQL function get_uppercase_film_names() line 3 at RETURN QUERY
  4. SQL 状态: 42804

这很奇怪,因为当我对film表执行选择查询时,title 明显是 character varying(255) 数据类型。它确实匹配,但不符合这个错误消息的要求。

我完全不知所措,所以任何帮助将不胜感激。

我尝试将返回查询强制转换为:

  1. 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:

  1. CREATE OR REPLACE FUNCTION get_uppercase_film_names()
  2. RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS
  3. $$
  4. BEGIN
  5. RETURN QUERY
  6. SELECT film.film_id, UPPER(film.title) AS uppercase_name
  7. FROM film;
  8. END;
  9. $$
  10. LANGUAGE plpgsql;

When I run this, it is successful.

When I go to call the function:

  1. SELECT film_id, uppercase_name FROM get_uppercase_film_names();

I get an error message pgadmin4:

  1. ERROR: structure of query does not match function result type
  2. DETAIL: Returned type text does not match expected type character varying in column 2.
  3. CONTEXT: PL/pgSQL function get_uppercase_film_names() line 3 at RETURN QUERY
  4. 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:

  1. 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,通常这是一个安全的选择,并且应该有助于避免您遇到的任何不匹配问题。

  1. CREATE OR REPLACE FUNCTION get_uppercase_film_names()
  2. RETURNS TABLE (film_id INTEGER, uppercase_name TEXT)
  3. AS
  4. $$
  5. BEGIN
  6. RETURN QUERY
  7. SELECT film.film_id, UPPER(film.title) AS uppercase_name
  8. FROM film;
  9. END;
  10. $$
  11. 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.

  1. CREATE OR REPLACE FUNCTION get_uppercase_film_names()
  2. RETURNS TABLE (film_id INTEGER, uppercase_name TEXT)
  3. AS
  4. $$
  5. BEGIN
  6. RETURN QUERY
  7. SELECT film.film_id, UPPER(film.title) AS uppercase_name
  8. FROM film;
  9. END;
  10. $$
  11. LANGUAGE plpgsql;

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

发表评论

匿名网友

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

确定