postgreSQL 中的自定义操作符

postgre是想对标Oracle的。所以在定义操作符上也对标了

操作符

看下面这条语句：

1. SELECT 3 OPERATOR(pg_catalog.+) 4 sum;  -- 1️⃣

复制代码

这条 SQL 看起来很怪，但它在 PostgreSQL 里是完全合法的，并且会正常返回 7。
实际上，它就是我们熟悉的

1. SELECT 3 + 4; -- 2️⃣

复制代码

1️⃣ 那行代码其实就是在玩 PostgreSQL 的一个“冷门但正式支持”的语法：显式使用 OPERATOR() 语法来调用操作符。
2️⃣这条语句执行时，PostgreSQL 内部会把 + 解析成一个真正的操作符对象，它的全名是 pg_catalog.+（在系统目录 pg_operator 里能查到）。而1️⃣就是把平时隐藏的内部机制直接写出来了，只不过是用最“啰嗦、最底层”的方式调用加法操作符，你可以把 OPERATOR(schema.操作符名) 理解成“强制指定用哪个操作符来操作左右两边”。
实际上，1️⃣还能写得更短：

1. SELECT 3 OPERATOR(+) 4;                     -- 可以省略 schema，默认 pg_catalog

复制代码

自定义操作符

PostgreSQL 目前具有主流数据库里最强的自定义操作符：

• 完全自定义新操作符
  
• 重载已有操作符（如重定义 +）
  
• 操作符可绑定索引（B-Tree, GiST, GIN…）
  
• 操作符可以有 commutator / negator
  
• 操作符直接影响优化器、索引选择
  

在这一方面，连Oracle也难以匹敌。

1. 语法

1. CREATE OPERATOR operator_name (
2.     { LEFTARG = left_type          -- 左操作数类型（单目操作符可省略）
3.     | RIGHTARG = right_type        -- 右操作数类型（单目操作符可省略）
4.     | BOTHARG = both_type }        -- 左右类型相同时代替上面两个
5.     [, PROCEDURE = function_name ] -- 必须：真正执行的函数
6.     [, COMMUTATOR = com_op ]       -- 可选：交换律操作符（如 + 和 + 本身）
7.     [, NEGATOR = neg_op ]          -- 可选：取反操作符（如 = 的取反是 <>）
8.     [, RESTRICT = res_proc ]       -- 可选：用于优化器选择性估计
9.     [, JOIN = join_proc ]          -- 可选：用于优化器连接估计
10.     [, HASHES ]                    -- 可选：支持 HASH JOIN 和 hash 聚合
11.     [, MERGES ]                    -- 可选：支持 MERGE JOIN
12. );

复制代码

2. 例子 1：创建 !!（双感叹号）前缀操作符，表示“转成大写”

1. -- 第1步：先创建一个底层函数
2. CREATE OR REPLACE FUNCTION immutable_upper(text)
3. RETURNS text AS $$
4.     SELECT upper($1);
5. $$ LANGUAGE sql IMMUTABLE STRICT;
7. -- 第2步：创建前缀操作符（右操作数，没有左操作数）
8. CREATE OPERATOR !! (
9.     RIGHTARG = text,                    -- 只有右操作数，在右边 → 前缀操作符
10.     PROCEDURE = immutable_upper         -- 调用上面那个函数
11. );
13. -- 第3步：试用
14. SELECT !! 'hello';        -- 返回 HELLO
15. SELECT !! column_name FROM users;

复制代码

不知道你有没有疑惑：这不还是用PG定义的函数吗？不还是PG本来就支持的东西吗？
没错。操作符只是一种“糖”，让你更方便、简洁的使用本来就有的能力。

3. 例子 2：创建自定义的 === 操作符，表示“可空相等”（带索引支持）

先创建函数

1. CREATE OR REPLACE FUNCTION geometry_strict_equal(anyelement, anyelement)
2. RETURNS boolean AS $$
3.     SELECT $1 IS NOT DISTINCT FROM $2;
4. $$ 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  表示任意类型的参数，但是两个参数类型要一样。
接下来创建操作符

1. CREATE OPERATOR === (
2.     LEFTARG = anyelement,
3.     RIGHTARG = anyelement,
4.     PROCEDURE = geometry_strict_equal,
5.     COMMUTATOR = ===,        -- 自己和自己交换律
6.     NEGATOR = !==,           -- 稍后会创建它的取反
7.     HASHES,                  -- 支持 hash join / hash agg
8.     MERGES                   -- 支持 merge join
9. );
11. -- 创建取反操作符 !==
12. CREATE OPERATOR !== (
13.     LEFTARG = anyelement,
14.     RIGHTARG = anyelement,
15.     PROCEDURE = geometry_strict_equal,
16.     NEGATOR = ===            -- 互相指向对方
17. );

复制代码

看一下例子：

比较的两个对象必须是同类型的，不然会报错，所以要明确指出null是什么类型。
如果是用在表查询语句中，因为表结构和字段类型是确定的，所以不用指出来。
4. 查询操作符

1. SELECT
2.     n.nspname      AS schema,
3.     o.oprname      AS operator, -- 操作符名称
4.     format_type(o.oprleft,  NULL) AS left_type,
5.     format_type(o.oprright, NULL) AS right_type,
6.     p.proname      AS function_name -- 函数名称
7. FROM pg_operator o
8. JOIN pg_namespace n ON n.oid = o.oprnamespace
9. JOIN pg_proc p ON p.oid = o.oprcode
10. WHERE n.nspname NOT IN ('pg_catalog')
11. and o.oprname = '!!'; -- 可以去掉过滤看看

复制代码

5. 删除操作符

1. DROP OPERATOR IF EXISTS !! (NONE, text); -- 先删除操作符，必须传左右两个参数，没有的写NONE
3. DROP FUNCTION public.immutable_upper(text); -- 函数如果还要用可以不删

复制代码

小练习

给 ilike 写一个操作符。我定义好函数了：

1. CREATE OR REPLACE FUNCTION chinese_ilike(text, text)
2. RETURNS boolean AS $$
3.     SELECT $1 ILIKE $2;
4. $$ LANGUAGE sql IMMUTABLE STRICT;

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！