PostgreSQL中INSERT调用rental_string自定义函数报错求助

创建了rental_string自定义函数，单独验证可正常运行，pgAdmin也确认函数和表结构无问题，但执行INSERT语句调用该函数时持续报错，已耗时超一小时。以下是相关代码及错误信息：

原代码 #

CREATE FUNCTION rental_string (title VARCHAR(250), rating VARCHAR(250))
RETURNS VARCHAR(250)
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN CONCAT(title,' - '  ,rating);
END;
$$;


INSERT INTO detailed_table 
    SELECT DISTINCT rental_string(f.title, f.rating), c.name, l.name
    FROM inventory AS i 
    JOIN film as f ON f.film_id = i.film_id
    JOIN film_category as fc ON fc.film_id = f.film_id 
    JOIN category AS c ON c.category_id = fc.category_id
    JOIN language AS l ON l.language_id = f.language_id
    GROUP BY f.title, c.name, l.name
    ORDER BY f.title;


CREATE TABLE detailed_table (
name_rating VARCHAR(250),
category VARCHAR(250),
film_language VARCHAR(250)
);

报错信息 #

ERROR: function rental_string(character varying, mpaa_rating) does not exist
LINE 3: SELECT DISTINCT rental_string(f.title, f.rating), c.name, l...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 47

问题原因 #

1. 执行顺序错误：INSERT语句在detailed_table创建之前执行，此时表不存在，会触发基础报错（你当前看到的参数问题是另一个核心错误，但顺序问题必须优先修正）。
2. 参数类型不匹配：film表的rating字段是PostgreSQL内置的mpaa_rating枚举类型，而自定义函数接收的是VARCHAR(250)类型，类型不兼容导致函数匹配失败。

修正方案 #

1. 调整SQL执行顺序：先创建表，再定义函数，最后执行INSERT操作。
2. 调用函数时将f.rating显式转换为VARCHAR类型。

修正后完整代码 #

-- 1. 先创建目标表
CREATE TABLE detailed_table (
name_rating VARCHAR(250),
category VARCHAR(250),
film_language VARCHAR(250)
);

-- 2. 定义自定义函数
CREATE FUNCTION rental_string (title VARCHAR(250), rating VARCHAR(250))
RETURNS VARCHAR(250)
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN CONCAT(title,' - '  ,rating);
END;
$$;

-- 3. 执行INSERT，显式转换枚举类型为字符串
INSERT INTO detailed_table 
    SELECT DISTINCT rental_string(f.title, CAST(f.rating AS VARCHAR(250))), c.name, l.name
    FROM inventory AS i 
    JOIN film as f ON f.film_id = i.film_id
    JOIN film_category as fc ON fc.film_id = f.film_id 
    JOIN category AS c ON c.category_id = fc.category_id
    JOIN language AS l ON l.language_id = f.language_id
    GROUP BY f.title, c.name, l.name
    ORDER BY f.title;

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