创建SQL自定义函数后生成列调用报'no such function'错误如何解决

错误原因 #

• 执行顺序错误：你先创建了GETAVERAGE函数，但函数依赖的properties表此时还未创建，函数本身就会创建失败，后续调用自然提示找不到函数。
• 自定义函数语法错误：你声明了函数返回NUMERIC类型，但函数内部仅给变量@avg赋值，没有加RETURN @avg语句，函数本身不符合语法规范。
• 数据库特性限制：绝大多数主流数据库（包括MySQL、PostgreSQL等）的虚拟生成列（GENERATED ALWAYS AS VIRTUAL）仅支持确定性的内置函数或常量表达式，不支持用户自定义的非标量函数、或者涉及表数据查询的函数，这类函数会被判定为不确定性，无法用于生成列定义。
• 语法适配问题：你写的GENERATED ALWAYS AS (...) VIRTUAL是SQLite、MySQL的语法，如果你用的是SQL Server，不需要加VIRTUAL关键字，直接写字段名 AS (表达式)即可。

可行解决方案 #

方案1：改用视图实现（兼容性最高，所有数据库通用） #

放弃使用计算列，创建视图来关联计算平均值，不需要调整表结构，也没有数据库兼容问题：

-- 先建基础表，注意建表顺序：先主表locations，再子表properties
CREATE TABLE IF NOT EXISTS locations
(
    location_id INTEGER PRIMARY KEY,
    name VARCHAR (50) UNIQUE
);

CREATE TABLE IF NOT EXISTS properties
(
    property_id INTEGER PRIMARY KEY,
    location_id INTEGER,
    address VARCHAR (100),
    value NUMERIC,
    FOREIGN KEY (location_id) REFERENCES locations (location_id)
);

-- 创建带平均值的视图
CREATE VIEW v_properties_with_avg AS
SELECT 
    p.*,
    AVG(p.value) OVER (PARTITION BY p.location_id) AS average_prop_value
FROM properties p;

后续查询直接查这个视图就能拿到对应location的房产平均值。

方案2：如果使用SQL Server（支持自定义函数作为计算列） #

调整创建顺序+修正函数语法即可：

-- 1. 先建两个基础表
CREATE TABLE IF NOT EXISTS locations
(
    location_id INTEGER PRIMARY KEY,
    name VARCHAR (50) UNIQUE
);

CREATE TABLE IF NOT EXISTS properties
(
    property_id INTEGER PRIMARY KEY,
    location_id INTEGER,
    address VARCHAR (100),
    value NUMERIC,
    FOREIGN KEY (location_id) REFERENCES locations (location_id)
);
GO

-- 2. 修正函数语法，加上RETURN
CREATE FUNCTION GETAVERAGE (@locationID AS INTEGER)
RETURNS NUMERIC
AS
BEGIN
    DECLARE @avg AS NUMERIC
    SELECT @avg = AVG(value) FROM properties WHERE location_id = @locationID
    RETURN @avg
END;
GO

-- 3. 最后加计算列
ALTER TABLE properties ADD average_prop_value AS (GETAVERAGE(location_id));

额外逻辑优化建议 #

同一个location下的所有房产的平均值是完全相同的，把这个字段放在properties表会产生大量冗余，你可以把平均值放在locations表，每次修改properties表的数据时用触发器同步更新locations表的平均值字段，查询效率更高。

内容的提问来源于stack exchange，提问作者Paul O.