我要评论postgre是想对标oracle的。所以在定义操作符上也对标了
操作符
看下面这条语句：
select 3 operator(pg_catalog.+) 4 sum; -- 1️⃣
这条 sql 看起来很怪，但它在 postgresql 里是完全合法的，并且会正常返回 7。
实际上，它就是我们熟悉的
select 3 + 4; -- 2️⃣
1️⃣ 那行代码其实就是在玩 postgresql 的一个“冷门但正式支持”的语法：显式使用 operator() 语法来调用操作符。
2️⃣这条语句执行时，postgresql 内部会把 + 解析成一个真正的操作符对象，它的全名是 pg_catalog.+（在系统目录 pg_operator 里能查到）。而1️⃣就是把平时隐藏的内部机制直接写出来了，只不过是用最“啰嗦、最底层”的方式调用加法操作符，你可以把 operator(schema.操作符名) 理解成“强制指定用哪个操作符来操作左右两边”。
实际上，1️⃣还能写得更短：
select 3 operator(+) 4; -- 可以省略 schema，默认 pg_catalog
自定义操作符
postgresql 目前具有主流数据库里最强的自定义操作符：
- 完全自定义新操作符
- 重载已有操作符（如重定义 +）
- 操作符可绑定索引（b-tree, gist, gin…）
- 操作符可以有 commutator / negator
- 操作符直接影响优化器、索引选择
在这一方面，连oracle也难以匹敌。
1. 语法
create operator operator_name (
{ leftarg = left_type -- 左操作数类型（单目操作符可省略）
| rightarg = right_type -- 右操作数类型（单目操作符可省略）
| botharg = both_type } -- 左右类型相同时代替上面两个
[, procedure = function_name ] -- 必须：真正执行的函数
[, commutator = com_op ] -- 可选：交换律操作符（如 + 和 + 本身）
[, negator = neg_op ] -- 可选：取反操作符（如 = 的取反是 <>）
[, restrict = res_proc ] -- 可选：用于优化器选择性估计
[, join = join_proc ] -- 可选：用于优化器连接估计
[, hashes ] -- 可选：支持 hash join 和 hash 聚合
[, merges ] -- 可选：支持 merge join
);2. 例子 1：创建 !!（双感叹号）前缀操作符，表示“转成大写”
-- 第1步：先创建一个底层函数 create or replace function immutable_upper(text) returns text as $$ select upper($1); $$ language sql immutable strict; -- 第2步：创建前缀操作符（右操作数，没有左操作数） create operator !! ( rightarg = text, -- 只有右操作数，在右边 → 前缀操作符 procedure = immutable_upper -- 调用上面那个函数 ); -- 第3步：试用 select !! 'hello'; -- 返回 hello select !! column_name from users;
不知道你有没有疑惑：这不还是用pg定义的函数吗？不还是pg本来就支持的东西吗？
没错。操作符只是一种“糖”，让你更方便、简洁的使用本来就有的能力。
3. 例子 2：创建自定义的 === 操作符，表示“可空相等”（带索引支持）
先创建函数
create or replace function geometry_strict_equal(anyelement, anyelement) returns boolean as $$ select $1 is not distinct from $2; $$ language sql immutable;
is not distinct from 是什么？这是 postgresql 特有的“空值安全的相等比较”
- 当 a = b → true
- 当 a 和 b 都是 null → true (普通的=，null = null → null (不为 true))
- 其他情况 → false
- 普通的=，null = null时 → null (不为 true)。
mysql中这个操作叫<=>“太空船运算符”，但是pg已经存在这个操作符了，主要在pg_trgm扩展中计算相似度，所以这里我们定义成===。
immutable 表示同样输入，永远返回同样的输出；可以用于索引；可以内联与优化。
anyelement 表示任意类型的参数，但是两个参数类型要一样。
接下来创建操作符
create operator === ( leftarg = anyelement, rightarg = anyelement, procedure = geometry_strict_equal, commutator = ===, -- 自己和自己交换律 negator = !==, -- 稍后会创建它的取反 hashes, -- 支持 hash join / hash agg merges -- 支持 merge join ); -- 创建取反操作符 !== create operator !== ( leftarg = anyelement, rightarg = anyelement, procedure = geometry_strict_equal, negator = === -- 互相指向对方 );
看一下例子：
比较的两个对象必须是同类型的，不然会报错，所以要明确指出null是什么类型。
如果是用在表查询语句中，因为表结构和字段类型是确定的，所以不用指出来。
4. 查询操作符
select
n.nspname as schema,
o.oprname as operator, -- 操作符名称
format_type(o.oprleft, null) as left_type,
format_type(o.oprright, null) as right_type,
p.proname as function_name -- 函数名称
from pg_operator o
join pg_namespace n on n.oid = o.oprnamespace
join pg_proc p on p.oid = o.oprcode
where n.nspname not in ('pg_catalog')
and o.oprname = '!!'; -- 可以去掉过滤看看5. 删除操作符
drop operator if exists !! (none, text); -- 先删除操作符，必须传左右两个参数，没有的写none drop function public.immutable_upper(text); -- 函数如果还要用可以不删
小练习
给 ilike 写一个操作符。我定义好函数了：
create or replace function chinese_ilike(text, text) returns boolean as $$ select $1 ilike $2; $$ language sql immutable strict;
到此这篇关于postgresql 中的自定义操作符示例详解的文章就介绍到这了,更多相关postgresql自定义操作符内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论