PostgreSQL扩展自定义运算符@*实现报错求助

错误原因 #

1. 函数返回类型不匹配：multiply_values函数定义返回my_type，但原脚本里将计算结果转换为int，与声明的返回类型矛盾。
2. ROW构造语法错误：ROW((left.value * right.value))中嵌套的多余括号导致PostgreSQL解析失败，这是触发语法错误的直接原因。
3. 升级脚本健壮性不足：原脚本未检查对象是否已存在，升级时可能引发重复创建类型/函数/运算符的错误。

解决方法与修正后的脚本 #

针对上述问题，修正后的版本更新脚本如下：

-- Create necessary objects for version 1.0.2

-- Custom Operator: @*
-- Description: Custom operator that multiplies two values of type my_type

-- 创建类型时检查是否已存在（升级脚本必备）
CREATE TYPE IF NOT EXISTS my_type AS (value int);

-- 定义运算符的核心函数，修正返回类型与ROW构造语法
CREATE OR REPLACE FUNCTION multiply_values(left my_type, right my_type) RETURNS my_type AS $$
    SELECT ROW(left.value * right.value)::my_type;
$$ LANGUAGE SQL IMMUTABLE;

-- 创建运算符时检查是否已存在
CREATE OPERATOR IF NOT EXISTS @* (
    PROCEDURE = multiply_values,
    LEFTARG = my_type,
    RIGHTARG = my_type
);

关键修正点说明 #

• 移除多余括号：将ROW((left.value * right.value))改为ROW(left.value * right.value)，符合PostgreSQL的ROW构造语法规范。
• 匹配返回类型：将类型转换目标从int改为my_type，确保函数返回值与声明的返回类型一致。
• 添加存在性检查：使用IF NOT EXISTS和CREATE OR REPLACE，避免升级时因对象已存在而报错，提升脚本的健壮性。

测试验证 #

执行修正后的脚本后，运行调用语句：

SELECT ROW(2)::my_type @* ROW(3)::my_type AS result;

将得到预期结果：

result
--------
 (6)
(1 row)

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