我去年在一个数据分析项目里被一个需求折腾得不轻业务方要求对几十个标签做“模糊匹配”而且要支持“任意标签命中即可”的写法SQL写出来又长又难维护。后来我把逻辑封装成了PostgreSQL自定义操作符一条语句干净利落同事看了直呼优雅。这篇文章就专门聊聊PostgreSQL里的自定义操作符——它到底是什么、怎么用、有哪些坑。如果你写过SQL、用过PostgreSQL但对它“可扩展”的底层能力还没有深入接触那这篇文章正好适合你。自定义操作符不是炫技它是PostgreSQL可扩展性体系里非常实用的一环我们可以自己定义符号让SQL语句更贴近业务语义也可以把复杂计算收敛到一个操作符中。接下来我会从原理、语法、实例到避坑完整走一遍。1. 先从几个“拧巴”的真实场景说起很多人第一次接触自定义操作符都是因为标准SQL里的运算符不够用。举几个我实际遇到过的场景你感受一下。第一个场景是标签系统的“部分命中”查询。商品表有一列tags存的是数组业务要筛出“包含标签集合里任意一个”的商品。用标准SQL写要么拆出子查询要么写一串tags ARRAY[...]。其实PostgreSQL内置了数组重叠操作符能解决但如果业务逻辑是“至少命中两个标签”标准写法就得倒腾jsonb函数或者多次ORSQL瞬间变得没法看。第二个场景是坐标距离排序。地图类应用要按“离用户从近到远”排序每次都要写sqrt(pow(lat1-lat2,2)pow(lng1-lng2,2))这种表达式。存到视图里还算能忍一旦涉及动态筛选条件就只能在应用层拼SQL或者把所有参数在SQL里展开代码重复度极高。第三个场景是相似度计算。比如商品标题之间要算相似度或者两个配置文本之间要算编辑距离。每次都要调函数similarity(a, b)读起来很不直觉。这些场景的共同点是业务上想要一个“符号化”的表达但SQL原生符号不支持。这时候自定义操作符就派上用场了——它可以把一个函数调用变成一个中缀运算符。也就是说你可以自己定义一个符号比如表示“包含关键词”-表示“求距离”让查询语句读起来跟普通算术表达式一样自然。这里我要先泼一盆冷水自定义操作符不是让你去扩展SQL语法它本质上是“给函数套一层中缀语法的壳”。后面第2节会详细讲这一点。搞懂这层本质你才能决定哪些场景值得用、哪些场景用了反而别扭。2. 操作符不是魔法函数、优先级和pg_operator的三角关系很多人以为CREATE OPERATOR是“创造了一个全新的运算能力”准确说法应该是它把已有的函数注册成了一个中缀符号。你把CREATE OPERATOR执行完PostgreSQL并不需要重新编译任何东西它只是在系统表pg_operator里多了一行记录记录“这个符号对应哪个函数”。来看一个最基础的例子CREATE OPERATOR ( FUNCTION same_text, LEFTARG text, RIGHTARG text );这条语句的意思是当我写abc abc数据库底层执行的是same_text(abc, abc)。所以自定义操作符的第一道门槛是你得先有一个函数。这个函数可以是普通SQL函数也可以是PL/pgSQL函数甚至可以是C语言扩展函数。函数写好之后操作符只是它的“入口”。在这个三角关系里还要提到一个很容易被忽略的角色——操作符优先级。SQL里1 2 * 3之所以结果是7是因为*的优先级高于。优先级不是写在pg_operator里的而是PostgreSQL语法分析器的固定规则。PostgreSQL对“自定义操作符”的处理方式很有意思所有“看不出类别的符号型操作符”统一分配一个中间优先级——高于比较运算符但低于普通的加减乘除。这里我用一张表汇总PostgreSQL预定义的主要优先级方便你后面理解自定义操作符被解析成什么顺序优先级从高到低运算符类别说明1.、::、[]字段访问、类型转换、下标2一元-、正负号3^幂运算4*、/、%乘除取模5、-加减6任意其他自定义符号操作符你自己造的操作符默认都在这一级7BETWEEN、IN、LIKE、ILIKE、SIMILAR等非符号类语法构造8、、、、、比较运算9IS、ISNULL、NOTNULL判空相关10NOT逻辑非11AND逻辑与12OR逻辑或注意第6级这是很多人踩坑的重灾区。你在SQL里写a - b c数据库会先算(a - b)再跟c做比较如果你期望的是“先比较 b 和 c再算 a 和比较结果的距离”那结果完全不对。所以我在自己的项目里立了一条规矩自定义操作符参与复杂表达式时一律显式加括号。这句话后面避坑部分还会反复强调。另一个容易忽略的调用方式是即使定义了操作符你也可以不用符号而是用函数式写法调用它因为它本质上就是函数-- 等价写法 SELECT abc abc; SELECT same_text(abc, abc);另外某些场景下你要强制把一个自定义操作符当运算符来用尤其是在函数内部或某些SQL语句的上下文中可以用OPERATOR(schema.)这种语法SELECT OPERATOR(pg_catalog.) (1, 2);这个写法看起来绕但当你定义了多个不同schema下的同名操作符需要区分使用时就很有用了。3. CREATE OPERATOR语法拆解不只是给符号起个名字走进CREATE OPERATOR的完整语法你会发现它像个“函数注册中心”。一张表就能看懂它的核心字段字段说明是否必填FUNCTION操作符底层调用的函数名称必填LEFTARG左侧参数类型除一元右操作符外必填RIGHTARG右侧参数类型除一元左操作符外必填COMMUTATOR交换子a op b 等价于 b op 另一个操作符 a可选NEGATOR否定子a op b 等价于 NOT (a 另一个操作符 b)可选RESTRICT用于优化器估算的选择性函数可选JOIN用于优化器估算的连接选择性函数可选HASHES是否可用于Hash Join相当于一个Hint可选MERGES是否可用于Merge Join可选3.1 FUNCTION是最核心的字段FUNCTION指定的函数有几个硬性条件函数必须已经存在且函数的参数个数必须和操作符的LEFTARG/RIGHTARG数量匹配。函数的返回类型会被当作操作符的结果类型。函数可以是任何语言写的PG内建函数也可以直接挂。比如我想定义一个text text的操作符判断左字符串是否包含右字符串中的所有词那我可以先建一个函数CREATE OR REPLACE FUNCTION match_all_keywords(source text, keywords text) RETURNS boolean AS $$ DECLARE kw text; BEGIN FOREACH kw IN ARRAY string_to_array(keywords, ) LOOP IF position(kw in source) 0 THEN RETURN false; END IF; END LOOP; RETURN true; END; $$ LANGUAGE plpgsql IMMUTABLE;然后CREATE OPERATOR ( FUNCTION match_all_keywords, LEFTARG text, RIGHTARG text );这样postgresql自定义操作符教程 postgresql 操作符返回的就是true因为两个关键词都出现了。3.2 LEFTARG和RIGHTARG决定操作符的“签名”这两个字段决定了操作符“长什么样”。一个操作符如果是二元的左右两个参数类型可以不一样比如text - integer。如果某个字段省略则表示这是一元操作符。PostgreSQL支持一元左操作符比如!在数字左边和一元右操作符比如阶乘符号在数值右边但实际项目里用得很少核心原因是“阅读不直觉”。一个容易出错的点自定义操作符的LEFTARG和RIGHTARG类型决定了调用时是否要做隐式类型转换。比如我定义了text text但你传入varcharPostgreSQL会在找不到完全匹配操作符时尝试用隐式转换如果varchar能隐式转成text那没问题。如果你传入的是integer大概率会报“操作符不存在”因为整数不会默认转成text。了解这一点能帮你少走很多弯路。3.3 优先级和结合性的“隐藏参数”前面说过优先级不是CREATE OPERATOR里能改的它是语法层面的固定规则。但有一个小细节同级别的多个操作符只有一个运算符时遵循左结合性。左结合意味着a op b op c会解析成(a op b) op c。你如果定义的操作符不满足结合律比如“a距离b再距离c”没有数学意义那就必须让使用方加上括号。3.4 优化器元数据COMMUTATOR、NEGATOR、HASHES、MERGES这一块是新手最容易忽略的但也是优化器能不能好好用你的操作符的关键。先说COMMUTATOR交换子。如果你定义的op满足“a op b 等价于 b op2 a”你就可以告诉PostgreSQL它俩是互为交换子的关系。例如和互为交换子。优化器知道这个信息后能在多表Join重排顺序时把带操作符的条件用在不同的表顺序上从而生成更好的执行计划。NEGATOR否定子同理。和互为否定子。如果你告诉优化器op1的否定子是op2优化器在某些情况下能把NOT (a op1 b)改写成a op2 b有机会利用索引。HASHES和MERGES则是“声明”你的操作符能不能用于Hash Join和Merge Join。这个声明有风险你必须保证函数行为真的符合哈希/归并语义——比如HASHES为true时底层函数必须满足“如果a和b相等hash值也一定相同”。如果你瞎声明优化器会按错的执行计划跑出错误结果而且很难排查。我的建议是不确定就不要写这两个参数留默认的false最多损失一点性能但正确性第一位。4. 从零实现两个可跑通的自定义操作符理论讲再多不如直接上手跑。我在这里带你把两个操作符完整写一遍。第一个是文本匹配第二个是坐标距离排序。两个都是实际项目中能直接用的场景。4.1 文本“包含任意关键词”操作符业务场景是商品搜索用户输入“无线 耳机 白色”希望返回标题里出现其中任意一个词的商品。SQL写成title ? 无线 耳机 白色语义一目了然。先写函数CREATE OR REPLACE FUNCTION text_match_any(source text, keywords text) RETURNS boolean AS $$ DECLARE kw text; BEGIN FOREACH kw IN ARRAY string_to_array(keywords, ) LOOP IF source LIKE % || kw || % THEN RETURN true; END IF; END LOOP; RETURN false; END; $$ LANGUAGE plpgsql IMMUTABLE;这里必须强调IMMUTABLE。这个标记告诉PostgreSQL同一个输入永远返回同一个输出不会依赖表、会话变量、时间等外部因素。只有IMMUTABLE函数才能被用于索引表达式。如果你写的是STABLE甚至VOLATILE索引相关的优化就绕开你了。这一点对自定义操作符极其重要后面还会提。然后创建操作符CREATE OPERATOR ? ( FUNCTION text_match_any, LEFTARG text, RIGHTARG text );测试SELECT 无线蓝牙耳机 ? 无线 耳机; -- true SELECT 有线键盘 ? 无线 耳机; -- false这个查询可以直接走表达式索引吗可以。你建一个表达式索引CREATE INDEX idx_product_title_match ON product ((title)); -- 注意这里只是示例对LIKE %term%这类模糊搜索走B-tree没有意义 -- 现实里一般用pg_trgm的gin索引。我举这个例子的重点不是索引优化本身而是强调函数属性影响优化器决策。真正做全文/模糊搜索PostgreSQL生态里已经有pg_trgm扩展这里只是演示“操作符内部做了什么”。4.2 坐标“欧氏距离”操作符第二个实例是给经纬度坐标求距离排序。虽然真实地图应用一般用PostGIS但如果你需要的是一个轻量、简单的平面距离自定义操作符完全够用。先定义坐标类型不需要。我们直接用point内置类型函数包裹一下CREATE OR REPLACE FUNCTION point_distance(a point, b point) RETURNS float8 AS $$ SELECT sqrt((a[0] - b[0]) ^ 2 (a[1] - b[1]) ^ 2); $$ LANGUAGE sql IMMUTABLE;这里用了一个技巧point类型可以按下标访问x和y下标0是x下标1是y。然后创建操作符CREATE OPERATOR - ( FUNCTION point_distance, LEFTARG point, RIGHTARG point );等等这里有个大坑PostgreSQL的cube扩展已经定义了-操作符如果你装了cube扩展再创建同名同类型或类型可混淆的操作符会报“操作符已存在”。所以我们在项目里最好先查一下自己的扩展环境SELECT oprname, oprleft::regtype, oprright::regtype FROM pg_operator WHERE oprname -;如果确认没有冲突或者能把冲突的类型区分开比如我定义的是point和pointcube用的是cube和cube两者类型不同其实是能共存的再执行CREATE OPERATOR。为了保险你也可以换个自定义符号比如~。测试SELECT point(0, 0) - point(3, 4); -- 结果5这个操作符在复杂SQL里就能写得很优雅SELECT shop_id, shop_location - point(116.40, 39.90) AS dist FROM shops ORDER BY dist LIMIT 20;注意我在ORDER BY子句里直接用dist别名是因为PostgreSQL允许别名用于ORDER BY。如果你需要在WHERE里过滤“距离小于3”则要写完整表达式或包一层子查询。5. 写完之后别急着上线边界条件和避坑清单自定义操作符能带来语法上的便利但用不好也会让你在排查问题上头大。这一节我把我踩过的坑以及团队里别人踩过的坑都列出来按严重程度排序。5.1 操作符名选择的隐藏限制不是所有符号都能用PostgreSQL对操作符名的限制不少而且有些限制很反直觉。操作符名可以由以下字符组成 - * / ~ ! # % ^ |?但有几个硬性规则不能以-或开头除非名字里还包含下面这些字符~ ! # % ^ |?不能包含--和/*以及*/因为这些是注释符会导致SQL解析错乱。::不能出现在操作符名里因为它是类型转换符号。这样的单字符操作符可以被覆盖但通常不建议容易引发混乱。比如CREATE OPERATOR -- ( ... )这种写法直接会报错因为SQL解析器看到的--是注释开头。我在一次分享里见过有人试图定义---作为操作符结果解析器把后面所有内容都当注释吞了查了一个下午才明白问题出在哪。5.2 优先级翻车现场记住那条铁律前面提到自定义操作符优先级固定排在“加减之后、比较之前”。这意味着SELECT 1 2 ? 3; -- 实际解析为1 (2 ? 3) 吗不?是自定义操作符优先级低于 -- 所以这里其实是 (1 2) ? 3等等我得修正一下。优先级低于加减意味着加减先结合。所以1 2 ? 3会解析成(1 2) ? 3。这个反直觉的点在复杂条件判断里尤其致命。比如WHERE a - b 5实际解析顺序是WHERE (a - b) 5。这个看着还算合理但如果写的是WHERE a - b c - d解析顺序是(a - b) (c - d)吗其实不一定要看-和的优先级。-优先级高所以先算两边的距离然后再比较两个距离。结果确实是你想的但如果你在中间混入了其他运算符就很容易出错。所以我在代码评审里看到自定义操作符参与复杂逻辑时标准批注是加括号求你了。5.3 操作符重载导致的歧义PostgreSQL允许同一符号对应不同参数类型组合例如?可以同时支持text ? text和tsvector ? text。如果你定义了好几个同名操作符调用时PG会根据参数类型自动选型。这本身很强大代价是排查问题时要多看一层。举个例子我给两个不同业务模块分别定义了jsonb ? text和text ? text。某天业务反馈“搜索不生效”我排查到SQL里写的是WHERE payload ? keyword但payload列是jsonb类型所以PG选择了jsonb ? text的操作符而这个操作符内部逻辑是“JSONB路径匹配”跟业务想要的“文本内容匹配”完全不是一回事。噪声很大当时找了好久才定位到是操作符重载的选择问题。我的建议是自定义操作符的数量控制在几个以内符号尽量选生僻组合不要把常用符号玩出花来。5.4 隐式类型转换操作符“找不到”的经典报错执行SELECT abc ? 123;会报什么大概率是“操作符不存在”。虽然数值123可以隐式转成text但PostgreSQL在操作符解析时不一定愿意做这种跨类型的隐式转换尤其涉及多个候选操作符时会更谨慎。解决办法之一是显式转换SELECT abc ? 123::text;或者你干脆给操作符再定义一个整数版本CREATE OPERATOR ? ( FUNCTION text_match_any_int, LEFTARG text, RIGHTARG integer );但这么做之前想清楚是不是真的需要这个类型组合还是说接口设计本身有问题。5.5 不可变函数声明与索引的心酸教训自定义操作符底层函数的IMMUTABLE属性非常重要我们再展开讲透。PostgreSQL文档对函数的稳定性分类有三个级别级别含义示例IMMUTABLE输入相同输出永远相同不依赖任何外部状态字符串长度、数学计算STABLE同一个查询里输入相同输出相同不同查询可能不同now() 在同一事务内不变VOLATILE同一查询里也可能变化random()、nextval()如果你的操作符底层函数被声明成VOLATILE那么它不能用在索引表达式里优化器也可能无法做某些优化。我见过有人写了一个“从配置表读取阈值再比较”的函数没加IMMUTABLE结果无法建表达式索引后来把配置值改成函数参数传入才解决。判断标准很简单函数是否会因为会话参数、表数据、时间变化而变只要会变就别标IMMUTABLE。否则优化器会用错误预设做优化结果可能是数据都变了索引还是旧值整个查询结果错得离谱。5.6 Search Path操作符也存在“名字空间”PostgreSQL的操作符跟表一样是有schema的。在CREATE OPERATOR时如果不指定schema操作符会创建在当前search_path的第一个schema里。查询时如果搜索路径不对可能找不到操作符或者找到另一个schema里的同名操作符。这个问题在多schema环境下特别容易踩。团队里某个人在app_v2schema里建了另一个人在publicschema里也建了如果类型定义稍微不同SQL执行计划就可能在不同schema下出现差异。建议所有生产环境自定义操作符统一放到同一个专用schema比如ext然后把ext加入search_path。这样既隔离了与内置操作符的潜在冲突也让团队知道“这个符号是自定义的”。5.7 与ORM和SQL生成工具的兼容性现在的业务代码很少纯手写SQL背后大概率有ORM。自定义操作符能不能被ORM正确识别我用过的经验是大部分ORM支持手写SQL片段或原生查询但如果你依赖ORM的查询构造器自动生成条件它不一定认识你的自定义符号。比如Python的SQLAlchemy如果你把自定义操作符写进column.op()(keywords)它是能生成对应SQL的但你必须显式告诉它这是自定义操作符而且ORM很难自动处理操作符优先级所以生成的SQL里括号往往很多。我的经验是自定义操作符适合用在哪适合用在报表查询、批量脚本、管理后台的固定SQL里不适合用在你团队所有业务开发都依赖的ORM主链路里。6. 什么时候该用、什么时候别用我的经验判断最后聊点个人体会。自定义操作符是PostgreSQL可扩展性的一顶王冠但不代表所有地方都得戴上它。值得用的场景在我看来有几个共同点同一套业务表达式在SQL中出现频率极高比如“距离”“交集”“相似度”“正则匹配”这类高频计算。内置函数写出来确实冗长符号化能显著提升SQL可读性。使用范围受控团队里能达成共识。不该用的场景同样很明确团队成员不熟悉很容易误用。操作符逻辑内部隐藏了业务规则排查问题时多了一层“黑盒”。与现有操作符冲突或者符号含义模糊。拿我们项目说吧最后真正保留下来的自定义操作符一个是用于关键词搜索一个是-用于坐标距离排序。其他演示性质的操作符后来都删了。为什么因为维护成本大于收益。如果你决定要上自定义操作符建议你把操作符的定义、底层函数、使用示例、限制条件都写进团队数据库规范文档里甚至建一个专门的schema来管理不要散落在各个业务脚本里。这样后来维护的人看到WHERE title 无线 耳机至少知道去哪查这个符号是什么意思。PostgreSQL的自定义操作符是个非常有意思的能力说实话深入使用它能刷新你对“关系数据库可扩展性”的认知。看完这篇你自己动手去建一个试试遇到报错也不怕先把函数写好、类型搞对、优先级记牢剩下的就是经验的积累了。
