PostgreSQL如何自定义运算符？手把手教你创建自己的操作符

PostgreSQL作为扩展性最强的关系型数据库之一，允许开发者几乎重写数据库的任何行为，运算符就是其中最有趣也最实用的扩展点之一。当内置的等号、大于小于这些符号无法贴切表达业务语义时，比如向量相似度、空间关系、自定义排序规则，定义一个自己的运算符能让查询语句变得优雅且高效。本文从底层函数到运算符声明，再到优化器支持，完整走一遍自定义运算符的实现过程。

运算符的本质：它是建立在函数之上的语法糖

要理解自定义运算符，先要明白PostgreSQL中运算符的真实身份。从数据库内核的视角看，一个运算符本质上只是一层薄薄的包装，它把一个符号（比如+@或者===）映射到一个具体的底层函数上。当你写下a + b时，执行器实际调用的可能是一个名为int4pl的函数。因此，创建运算符的第一步永远是先创建那个承担实际计算工作的函数。

这个底层函数必须是普通SQL函数或者用C等语言编写的函数，它接收一到两个参数（PostgreSQL不支持一元运算符的自定义，实际上支持的只有二元运算符），返回一个结果。参数类型可以是你自定义的复合类型、枚举，甚至内置类型，这带来了极大的灵活性。比如你可以定义两个整数之间的求模比较，也可以定义两个JSONB之间的某种业务规则判断。

下面通过一个实际需求来演示：假设系统存储了二维向量，需要比较两个向量的欧氏距离是否小于某个阈值，我们定义一个距离运算符来完成计算。先创建类型和底层函数：

-- 定义一个简单的二维向量类型
CREATE TYPE vec2 AS (x float8, y float8);

-- 创建承担实际计算的底层函数
CREATE OR REPLACE FUNCTION vec2_distance(a vec2, b vec2)
RETURNS float8 AS $$
BEGIN
    RETURN sqrt(
        power(a.x - b.x, 2) + power(a.y - b.y, 2)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- 测试底层函数是否正常工作
SELECT vec2_distance(ROW(0, 0)::vec2, ROW(3, 4)::vec2);
-- 返回 5

注意函数声明中的IMMUTABLE和STRICT两个标记非常重要。IMMUTABLE告诉优化器同样的输入永远产生同样的输出，这意味着涉及索引扫描和常量折叠的优化可以放心进行；而STRICT表示任一参数为NULL时直接返回NULL，不必执行函数体。这两个属性会直接影响后续运算符在查询计划中的表现，写错了可能导致索引失效或者结果不符合预期。

使用CREATE OPERATOR正式注册运算符

底层函数就绪后，就可以用CREATE OPERATOR语句把它包装成一个可以写在表达式中的符号了。运算符名字可以由以下字符组合而成：加号、减号、星号、斜杠、小于、大于、等号、波浪线、感叹号、at符号、井号、百分号、幂符号、与符号、竖线以及反引号，长度限制在63个字符以内（NAMEDATALEN减一）。不过有几个限制需要注意：不能包含冒号和逗号，也不能和现有注释符号--以及多行注释/*冲突。

继续上面的向量例子，我们定义一个<=>运算符，用于返回两点之间的距离，这样在查询里就能直接写两个向量之间的距离值：

CREATE OPERATOR <=> (
    LEFTARG = vec2,
    RIGHTARG = vec2,
    FUNCTION = vec2_distance,
    COMMUTATOR = <=>
);

-- 现在可以像使用内置运算符一样使用它
SELECT ROW(0, 0)::vec2 <=> ROW(3, 4)::vec2;
-- 返回 5

-- 结合WHERE子句做距离过滤
SELECT *
FROM places
WHERE p.location <=> ROW(116.4, 39.9)::vec2 < 10;

语句中的几个属性各有用途。LEFTARG和RIGHTARG声明运算符左右操作数的类型，如果省略LEFTARG则表示这是一个右一元运算符。可以看到我们在同一条语句里用了小于号，由于<=>运算符返回的是数值，它天然可以和内置的小于号继续组合成过滤条件，这正是运算符可组合性的体现。

COMMUTATOR声明交换律运算符，意思是a OP b等价于b OP a。这个声明对优化器意义重大：当查询优化器尝试把表达式改写成更优的形式（例如把常量移到索引有利的一侧）时，需要知道运算符是否满足交换律。类似的高级属性还有NEGATOR（声明取反运算符，比如等于和不等于是互为取反）、RESTRICT（指定选择性估算函数，帮助优化器估算结果行数）、JOIN（指定连接选择性估算函数）以及MERGES和HASHES（声明该运算符可用于合并连接和哈希连接）。

让运算符支持索引：策略与操作符类

定义了运算符只是第一步，如果希望在WHERE条件中使用它时能走索引，还需要把它注册到索引访问方法中。B-tree索引支持五种标准策略：小于、小于等于、等于、大于等于、大于，每种策略对应一个策略编号。通过把这些编号和你的自定义函数关联起来，数据库才能在索引扫描时利用你的运算符做查找。

这一步通过CREATE OPERATOR CLASS来完成。假设我们想让上面的距离比较能被GiST或朴素B-tree索引加速，一个常见的做法是定义一个基于距离的比较族：

-- 先定义比较函数，供B-tree策略使用
CREATE OR REPLACE FUNCTION vec2_cmp(a vec2, b vec2)
RETURNS int4 AS $$
BEGIN
    IF vec2_distance(ROW(0,0)::vec2, a) <
       vec2_distance(ROW(0,0)::vec2, b) THEN
        RETURN -1;
    ELSIF vec2_distance(ROW(0,0)::vec2, a) >
          vec2_distance(ROW(0,0)::vec2, b) THEN
        RETURN 1;
    END IF;
    RETURN 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- 创建操作符类，将运算符注册到B-tree策略中
CREATE OPERATOR CLASS vec2_btree_ops
    DEFAULT FOR TYPE vec2 USING btree AS
    OPERATOR    1   <,
    OPERATOR    2   <=,
    OPERATOR    3   =,
    OPERATOR    4   >=,
    OPERATOR    5   >,
    FUNCTION    1   vec2_cmp(vec2, vec2);

这段代码里，策略编号1到5分别对应五种排序关系，FUNCTION部分提供支撑排序的比较函数。注册完成后，在vec2类型的列上建立B-tree索引，涉及这些运算符的查询条件就有机会使用索引扫描了。用EXPLAIN验证一下执行计划，如果看到Index Scan而不是Seq Scan，说明优化器已经认可你的运算符。

需要注意：不是所有自定义运算符都值得建索引支持。如果运算符的选择性很高（比如等值匹配），B-tree很合适；如果是范围或相似度类运算符，GiST、SP-GiST或GIN这类访问方法才是正确选择，向量场景下业界广泛使用的pgvector扩展就是基于GiST和HNSW实现的完整运算符族，可以作为学习的范例。

实战建议与常见的坑

在实际项目中定义运算符时，有几条经验值得遵循。第一，运算符应该用于语义清晰、高频使用的操作。如果一个比较逻辑全项目只出现一两次，直接写函数调用反而更直观，定义运算符只会增加维护者的理解成本。像@>包含、<@>距离这类语义直观的运算符才是好设计，而把业务上含义模糊的逻辑塞进符号里则会让SQL变得难读。

第二，留意运算符重载的解析规则。PostgreSQL根据操作数类型选择具体的运算符实现，同一个符号可以针对不同类型有不同定义。这很强大，但也意味着如果同一对操作数类型存在多个匹配的运算符定义，会报出ambiguous错误。定义前先用\do命令或查询pg_operator系统目录确认没有冲突：

-- 查看当前已存在的 <=> 运算符定义
SELECT n.nspname AS schema, o.oprname,
       t1.typname AS left_type, t2.typname AS right_type
FROM pg_operator o
LEFT JOIN pg_type t1 ON o.oprleft = t1.oid
LEFT JOIN pg_type t2 ON o.oprright = t2.oid
JOIN pg_namespace n ON o.oprnamespace = n.oid
WHERE o.oprname = '<=>';

-- 删除运算符和底层函数
DROP OPERATOR <=>(vec2, vec2);
DROP FUNCTION vec2_distance(vec2, vec2);

第三，权限与模式管理。自定义运算符默认创建在当前搜索路径的第一个模式下，建议放到专门的扩展模式中并授予合适权限。删除时要注意依赖顺序，必须先删运算符再删底层函数，否则会因为依赖关系而失败。另外，如果打算把运算符打包成扩展发布，记得在扩展的控制文件和SQL脚本中按依赖顺序编排好创建语句。

总的来说，PostgreSQL的自定义运算符机制展示了这款数据库“把一切开放给用户”的设计哲学。从底层函数、运算符声明到策略和操作符类，整条链路环环相扣，理解了这条链路，你就掌握了扩展数据库领域语言的钥匙。下次当你的查询因为缺乏合适的操作语义而显得臃肿时，不妨考虑定义一个属于自己业务的运算符，让SQL回归它应有的简洁与表达力。

PostgreSQL自定义运算符CREATE OPERATOR修改时间：2026-09-10 11:48:15