函数(function)是一些预定义好的代码模块,可以将输入进行计算和处理,最终输出一个结果值。
PostgreSQL 可以分为两类:标量函数(scalar function)和聚合函数(aggregation function)。标量函数针对每个输入都会返回相应的结果,聚合函数针对一组输入汇总出一个结果。在第 13 篇中已经介绍了几个常见的聚合函数。
本篇主要介绍 PostgreSQL 提供的标量函数。为了便于学习,可以将常见的系统函数分为以下类别:数学函数、字符函数、日期时间函数以及类型转换函数。
除了可以使用这些系统内置的函数之外,PostgreSQL 也支持创建自定义的函数(UDF)。
数学函数
数学函数和运算符用于执行算术运算,输入和输出通常都是数字类型。
算术运算符
PostgreSQL 支持以下算术运算符:
运算符 | 描述 | 示例 | 结果 |
+ | 加法 | 2 + 3 | 5 |
- | 减法 | 2 - 3 | -1 |
* | 乘法 | 2 * 3 | 6 |
/ | 整除 | 5 / 2 | 2 |
% | 模除(求余) | 5 % 4 | 1 |
^ | 求幂(左边为底数,右边为指数) | 2.0 ^ 3.0 | 8 |
|/ | 平方根 | |/ 25.0 | 5 |
||/ | 立方根 | ||/ 27.0 | 3 |
! | 阶乘 | 5 ! | 120 |
!! | 阶乘(前置运算符) | !! 5 | 120 |
@ | 绝对值 | @ -5.0 | 5 |
& | 按位与 | 91 & 15 | 11 |
| | 按位或 | 32 | 3 | 35 |
# | 按位异或 | 17 # 5 | 20 |
~ | 按位非 | ~1 | -2 |
<< | 按位左移 | 1 << 4 | 16 |
>> | 按位右移 | 8 >> 2 | 2 |
其中,按位运算只对整型数字类型有效;左移 N 位相当于乘以 2 的 N 次方,右移 N 位相当于除以 2 的 N 次方。
绝对值
abs(x) 函数用于计算 x 的绝对值。例如:
取整函数
ceil(dp)/ceiling(dp) 函数用于计算大于或等于 dp 的最小整数;floor(dp) 函数用于计算小于或等于 dp 的最大整数;round(dp) 函数四舍五入为整数;trunc(dp) 函数向零取整。
另外,round(dp, s) 函数四舍五入到 s 位小数;trunc(dp, s) 函数截断到 s 位小数。
乘方与开方
power(a, b) 函数计算 a 的 b 次方;sqrt(dp) 函数计算 dp 的平方根;cbrt(dp) 函数计算 dp 的立方根。
指数与对数
exp(dp) 函数计算以自然常数 e 为底的指数,ln(dp) 函数计算以自然常数 e 为底数的对数,log(dp)/log10(dp) 函数计算以 10 为底的对数,log(b, x) 函数计算以 b 为底的对数。
整数商和余数
div(y, x) 函数计算 y 除以 x 的整数商,mod(y, x) 函数计算 y 除以 x 的余数。
弧度与角度
degrees(dp) 函数用于将弧度转为角度,radians(dp) 函数用于将角度转弧度。
常量 π
pi() 函数用于返回常量“π”的值。
符号函数
sign(dp) 返回参数的正负号,可能的结果为 -1、0、+1。
生成随机数
PostgreSQL 提供了用于返回一个随机数的函数 random()。
random() 返回一个大于等于 0 小于 1 的随机数,类型为双精度浮点数。
另外,setseed(dp) 函数可以为随后一次运行的 random() 函数设置种子数,参数范围:-1.0 <= dp <= 1.0。
相同的种子可以得到相同的随机数,用于重现结果。
📝PostgreSQL 还提供了常用的三角函数和双曲函数,具体参考官方文档。
字符函数
字符函数和运算符用于处理字符串数据。
字符串连接
concat(str, …) 函数用于连接字符串,并且忽略其中的 NULL 参数;concat_ws(sep, str, …) 函数使用指定分隔符 sep 连接字符串。
两个竖杠(||)也可以用于连接字符串,但是 NULL 参数将会返回 NULL。
字符与编码
ascii(string) 函数返回第一个字符的 ASCII 码。对于 UTF8 返回 Unicode 码;对于其他多字节编码,参数必须是一个 ASCII 字符。
chr(int) 函数返回编码对应的字符。对于 UTF8,参数指定的是 Unicode 码;对于其他多字节编码,参数必须对应一个 ASCII 字符。参数不允许为 0(空字符),因为 text 数据类型不能存储空字符。
字符串长度
bit_length(string) 函数计算字符串包含的比特数;length(string)/char_length(string)/character_length(string) 函数计算字符串包含的字符数;octet_length(string) 函数计算字符串包含的字节数。
大小写转换
lower(string) 函数将字符串转换为小写形式,upper(string) 函数将字符串转换为大写形式,initcap(string) 函数将每个单词的首字母大写,其他字母小写。
子串查找与替换
substring(string [from] [for]) 函数用于提取从位置 from 开始的 for 个字符子串,位置从 1 开始计算。substr(string, from [, count]) 的作用相同。
left(str, n) 函数返回字符串左边的 n 个字符。如果 n 为负数,返回除了最后 |n| 个字符之外的所有字符。
right(str, n) 函数返回字符串右边的 n 个字符。如果 n 为负数,返回除了左边 |n| 个字符之外的字符。
substring(string from pattern) 函数提取匹配 POSIX 正则表达式的子串。
substring(string from pattern for escape) 函数提取匹配 SQL 正则表达式的子串。
regexp_match(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的第一个子串。
regexp_matches(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的所有子串,结果是一个集合。
position(substring in string) 返回子串的位置;strpos(string, substring) 函数的作用相同,但是参数顺序相反。
starts_with(string, prefix) 函数判断 string 是否以 prefix 开头,如果是则返回 true;否则返回 false。
replace(string, from, to) 函数将字符串 string 中的 from 子串替换为 to 子串;regexp_replace(string, pattern, replacement [, flags]) 函数字符串 string 中匹配 POSIX 正则表达式 pattern 的子串替换为 replacement。
translate(string , from , to) 函数将字符串 string 中出现在 from 中的字符串替换成 to 中相应位置的字符。如果 from 长度大于 to,在 to 中没有对应值的字符将被删除。
overlay(string placing substring from [for]) 函数使用 substring 覆盖字符串 string 中从 from 开始的 for 个字符。
截断与填充
trim([leading | trailing | both] [characters] from string) 函数从字符串的开头(leading)、结尾(trailing)或者两端(both)删除由指定字符 characters(默认为空格)组成的最长子串;trim([leading | trailing | both] [from] string [, characters]) 函数的作用相同。
btrim(string [, characters]) 函数的作用与上面 trim 函数的 both 选项相同;ltrim(string [, characters]) 与上面 trim 函数的 leading 选项相同;rtrim(string [, characters]) 函数上面 trim 函数的 trailing 选项相同。
lpad(string, length [, fill ]) 函数在 string 左侧使用 fill 中的字符(默认空格)进行填充,直到长度为 length。如果 string 长度大于 length,从右侧截断到长度 length。
rpad(string, length [, fill ]) 函数在 string 右侧使用 fill 中的字符(默认空格)进行填充,直到长度为 length。如果 string 长度大于 length,从右侧截断到长度 length。
repeat(string, number) 函数将字符串 string 重复 N 次。
字符串格式化
format(formatstr , formatarg) 用于对字符串格式化,类似于 C 语言中的 sprintf 函数。
📝关于 format 函数的格式化参数可以参考官方文档。
MD5 值
md5(string) 函数用于返回十六进制格式的 MD5 值。
字符串拆分
regexp_split_to_table(string, pattern[, flags]) 函数用于拆分字符串,使用 POSIX 正则表达式作为分隔符。函数的返回类型是 text 集合。
split_part(string, delimiter, field) 函数使用 delimiter 拆分字符串,并返回指定项(从 1 开始计数)。
字符串反转
reverse(str) 函数用于将字符串反转。
📝更多字符函数可以参考官方文档。