第十一章中的函数解读（1）-编程阁

第一个函数

create or replace function ST_P2PDistance(x1 float, y1 float, x2 float, y2 float) returns float as $$ begin return sqrt((x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1)); end; $$ language plpgsql;

第一行：函数定义

create or replace function ST_P2PDistance(x1 float, y1 float, x2 float, y2 float)

create or replace function: 创建或替换函数
- 如果函数不存在，则创建新函数
- 如果同名函数已存在，则替换它
ST_P2PDistance: 函数名
- ST_前缀：遵循空间函数命名约定（SQL/MM标准）
- P2P：Point to Point（点到点）
- Distance：表示这是计算距离的函数
(x1 float, y1 float, x2 float, y2 float): 参数列表
- 四个浮点数参数：第一个点的x,y坐标，第二个点的x,y坐标
- x1, y1: 第一个点的坐标
- x2, y2: 第二个点的坐标
- float: 参数类型，浮点数

第二行：返回值声明

returns float as $$

returns float: 声明函数返回一个浮点数
- 计算结果是两点之间的欧几里得距离
as $$: 开始函数体
- $$是"美元符号引用"，用于定义函数体的开始和结束
- 这是PostgreSQL中定义多行字符串的常用方式
- 也可以使用单引号，但$$更方便（无需转义内部引号）

第三行：函数体开始

begin

begin: 标记PL/pgSQL函数体的开始
- PL/pgSQL是PostgreSQL的过程化语言扩展
- 所有PL/pgSQL函数都以begin开始

第四行：计算逻辑

return sqrt((x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1));

这是函数的核心计算逻辑：

计算过程分解：

(x2 - x1) * (x2 - x1): 计算x坐标差的平方
- (x2 - x1): 两点在x轴上的距离
- 乘以自身：得到平方，确保值为正
(y2 - y1) * (y2 - y1): 计算y坐标差的平方
- (y2 - y1): 两点在y轴上的距离
- 乘以自身：得到平方
相加:(x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1)
- 根据勾股定理：斜边² = 直角边1² + 直角边2²
- 这里得到的是两点间直线距离的平方
sqrt(...): 计算平方根
- 将平方和开方，得到实际的直线距离
- sqrt()是PostgreSQL内置的数学函数

数学公式：

距离=(x2−x1)2+(y2−y1)2距离=(x2−x1)2+(y2−y1)2

第五行：函数体结束

end;

end: 标记PL/pgSQL函数体的结束与begin配对使用

第六行：语言声明

$$ language plpgsql;

$$: 结束函数体的"美元符号引用"
- 与第二行的$$配对
language plpgsql: 声明函数使用PL/pgSQL语言编写
- PostgreSQL支持多种函数语言：SQL、PL/pgSQL、Python等
- PL/pgSQL是专门为PostgreSQL设计的过程化语言

完整示例：如何使用这个函数

-- 示例1：计算点(0,0)和点(3,4)之间的距离 SELECT ST_P2PDistance(0, 0, 3, 4); -- 返回：5.0 (因为3²+4²=9+16=25，√25=5) -- 示例2：在查询中使用 SELECT city1, city2, ST_P2PDistance(x1, y1, x2, y2) as distance_km FROM city_pairs;

重要说明：

1. 坐标系假设

这个函数假设：

使用平面直角坐标系（欧几里得几何）
不适用于地理坐标系（经纬度）
- 对于经纬度坐标，需要使用球面距离公式（如Haversine公式）
- 或者在GEOGRAPHY类型上使用ST_Distance()函数

2. 与SQL Server Spatial的区别

在SQL Server Spatial中，相同功能可以通过以下方式实现：

-- SQL Server Geometry类型 DECLARE @p1 geometry = geometry::Point(x1, y1, 0); DECLARE @p2 geometry = geometry::Point(x2, y2, 0); SELECT @p1.STDistance(@p2); -- 或者使用内置函数 SELECT geometry::Point(x1, y1, 0).STDistance(geometry::Point(x2, y2, 0));

3. 可能的改进

-- 改进版：添加参数验证和注释 CREATE OR REPLACE FUNCTION ST_P2PDistance( x1 float, y1 float, x2 float, y2 float ) RETURNS float AS $$ DECLARE dx float; dy float; BEGIN -- 计算坐标差 dx := x2 - x1; dy := y2 - y1; -- 返回欧几里得距离 RETURN sqrt(dx * dx + dy * dy); END; $$ LANGUAGE plpgsql IMMUTABLE -- 表示函数是幂等的（相同输入总是相同输出） STRICT -- 如果任何参数为NULL，则返回NULL PARALLEL SAFE; -- 可以安全地并行执行

这个函数是一个简单的欧几里得距离计算器，适用于平面坐标系中的两点距离计算。

第二个函数

-- 自定义比较函数 CREATE FUNCTION fruity_qty_larger_than(left_fruit FRUIT_QTY, right_fruit FRUIT_QTY) RETURNS BOOL AS $$ ... $$ LANGUAGE plpgsql; -- 绑定为>操作符 CREATE OPERATOR > (leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, procedure = fruit_qty_larger_than, commutator = >);

第一部分：自定义比较函数定义

-- 自定义比较函数

这是一个注释，说明接下来要创建自定义比较函数
--是 SQL 的单行注释符号

CREATE FUNCTION fruity_qty_larger_than(left_fruit FRUIT_QTY, right_fruit FRUIT_QTY) RETURNS BOOL AS $$ ... $$ LANGUAGE plpgsql;

函数声明部分：

CREATE FUNCTION fruity_qty_larger_than

CREATE FUNCTION：创建新函数的 SQL 命令
fruity_qty_larger_than：函数名称
- 这是一个自定义命名，描述了函数的功能："水果数量大于比较"

参数定义部分：

(left_fruit FRUIT_QTY, right_fruit FRUIT_QTY)

参数列表，定义两个输入参数：
- left_fruit：第一个参数名，代表比较操作符左边的操作数
- right_fruit：第二个参数名，代表比较操作符右边的操作数
- FRUIT_QTY：两个参数的类型
  - 这是一个用户自定义的复合类型（如之前的CREATE TYPE FRUIT_QTY定义）
  - 可能包含类似(fruit_name TEXT, quantity INT)这样的字段

返回类型：

RETURNS BOOL

指定函数返回布尔值（TRUE或FALSE）
表示比较结果：真（大于）或假（不大于）

函数体占位符：

AS $$ ... $$

$$ ... $$：美元符号引用的函数体
...：这里是占位符，表示实际的函数实现代码被省略了

实际的实现可能类似于：

AS $$ BEGIN -- 比较逻辑，比如： -- 1. 先比较 quantity 字段 -- 2. 如果 quantity 相等，再按 fruit_name 排序 IF left_fruit.quantity > right_fruit.quantity THEN RETURN TRUE; ELSIF left_fruit.quantity < right_fruit.quantity THEN RETURN FALSE; ELSE -- quantity相等时，按fruit_name比较 RETURN left_fruit.fruit_name > right_fruit.fruit_name; END IF; END; $$

语言声明：

LANGUAGE plpgsql;

指定函数用PL/pgSQL语言编写
PostgreSQL 支持多种过程化语言

第二部分：操作符绑定

CREATE OPERATOR > (leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, procedure = fruit_qty_larger_than, commutator = >);

操作符创建：

CREATE OPERATOR >

CREATE OPERATOR：创建新操作符的命令
>：要创建/定义的操作符符号
- 这是标准的"大于"比较操作符
- PostgreSQL 允许为用户自定义类型重载现有操作符

操作符属性定义：

(leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, ...)

leftarg = FRUIT_QTY：指定左操作数的类型为FRUIT_QTY
rightarg = FRUIT_QTY：指定右操作数的类型为FRUIT_QTY
这意味着这个>操作符将用于比较两个FRUIT_QTY类型的值

procedure = fruit_qty_larger_than

关键连接：指定当使用>操作符时，实际调用的函数
fruit_qty_larger_than：前面定义的函数名
- 注意：这里写的是fruit_qty_larger_than（单数），而前面函数是fruity_qty_larger_than（复数）
- 这可能是个笔误，或者故意使用不同名称（但通常应该一致）
- 如果名称不匹配，创建操作符时会报错

commutator = >

交换子声明：这是一个高级优化属性
commutator：指定该操作符的交换操作符
=：这里设置为自身>，表示：
- 如果a > b为真，那么b < a也应该为真
- PostgreSQL 查询优化器可以利用这个信息：
  - 当看到b < a时，知道可以转换为a > b
  - 可能利用索引或优化查询计划

实际上，对于>操作符，其交换子应该是<：

-- 更正确的写法可能是： CREATE OPERATOR > (..., commutator = <); -- 并且还需要定义对应的 < 操作符： CREATE FUNCTION fruity_qty_smaller_than(left FRUIT_QTY, right FRUIT_QTY) RETURNS BOOL AS $$ ... $$; CREATE OPERATOR < (leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, procedure = fruity_qty_smaller_than, commutator = >);

完整示例：实际使用场景

-- 1. 首先定义复合类型 CREATE TYPE FRUIT_QTY AS ( fruit_name TEXT, quantity INT ); -- 2. 实际函数实现（非占位符） CREATE FUNCTION fruity_qty_larger_than(left_fruit FRUIT_QTY, right_fruit FRUIT_QTY) RETURNS BOOL AS $$ BEGIN -- 主要按数量比较，数量相同则按名称字典序比较 IF left_fruit.quantity > right_fruit.quantity THEN RETURN TRUE; ELSIF left_fruit.quantity < right_fruit.quantity THEN RETURN FALSE; ELSE -- quantity相等时，比较fruit_name RETURN left_fruit.fruit_name > right_fruit.fruit_name; END IF; END; $$ LANGUAGE plpgsql; -- 3. 创建操作符（修正函数名一致） CREATE OPERATOR > ( leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, procedure = fruity_qty_larger_than, -- 这里名称必须与函数名一致 commutator = > -- 实际上可能需要先定义 < 操作符 ); -- 4. 使用示例 SELECT (('apple', 10)::FRUIT_QTY) > (('banana', 5)::FRUIT_QTY) as result; -- 返回: TRUE (因为10 > 5) SELECT (('apple', 5)::FRUIT_QTY) > (('banana', 5)::FRUIT_QTY) as result; -- 返回: FALSE (因为数量相等，但'apple' < 'banana'字典序)