文章转载自公众号:SQL编程思想
函数(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 的绝对值。例如:
SELECT abs(-17.4);
| abs |
|------|
| 17.4 |
取整函数
ceil(dp)/ceiling(dp) 函数用于计算大于或等于 dp 的最小整数;floor(dp) 函数用于计算小于或等于 dp 的最大整数;round(dp) 函数四舍五入为整数;trunc(dp) 函数向零取整。
SELECT ceil(-42.8), floor(-42.8), round(12.45), trunc(12.8);
| ceil | floor | round | trunc |
|------|-------|-------|-------|
| -42 | -43 | 12 | 12 |
另外,round(dp, s) 函数四舍五入到 s 位小数;trunc(dp, s) 函数截断到 s 位小数。
乘方与开方
power(a, b) 函数计算 a 的 b 次方;sqrt(dp) 函数计算 dp 的平方根;cbrt(dp) 函数计算 dp 的立方根。
SELECT power(2, 3), sqrt(4), cbrt(27);
| power | sqrt | cbrt |
|-------|------|--------------------|
| 8 | 2 | 3.0000000000000004 |
指数与对数
exp(dp) 函数计算以自然常数 e 为底的指数,ln(dp) 函数计算以自然常数 e 为底数的对数,log(dp)/log10(dp) 函数计算以 10 为底的对数,log(b, x) 函数计算以 b 为底的对数。
SELECT exp(1.0), ln(2.718281828459045), log(100), log(2.0, 16.0);
| exp | ln | log | log |
|-------------------|--------------------|-----|-----|
| 2.718281828459045 | 0.9999999999999999 | 2 | 4 |
整数商和余数
div(y, x) 函数计算 y 除以 x 的整数商,mod(y, x) 函数计算 y 除以 x 的余数。
SELECT div(9,4), mod(9,4);
| div | mod |
|-----|-----|
| 2 | 1 |
弧度与角度
degrees(dp) 函数用于将弧度转为角度,radians(dp) 函数用于将角度转弧度。
SELECT degrees(1.57), radians(90.0);
| degrees | radians |
|-------------------|--------------------|
| 89.95437383553924 | 1.5707963267948966 |
常量 π
pi() 函数用于返回常量“π”的值。
SELECT pi();
| pi |
|-------------------|
| 3.141592653589793 |
符号函数
sign(dp) 返回参数的正负号,可能的结果为 -1、0、+1。
SELECT sign(-8.4);
| sign |
|------|
| -1 |
生成随机数
PostgreSQL 提供了用于返回一个随机数的函数 random()。
SELECT random();
| random |
|---------------------|
| 0.07772749848663807 |
random() 返回一个大于等于 0 小于 1 的随机数,类型为双精度浮点数。
另外,setseed(dp) 函数可以为随后一次运行的 random() 函数设置种子数,参数范围:-1.0 <= dp <= 1.0。
SELECT setseed(0);
SELECT random();
| random |
|--------------------|
| 0.8401877167634666 |
相同的种子可以得到相同的随机数,用于重现结果。
📝PostgreSQL 还提供了常用的三角函数和双曲函数,具体参考官方文档。
字符函数
字符函数和运算符用于处理字符串数据。
字符串连接
concat(str, …) 函数用于连接字符串,并且忽略其中的 NULL 参数;concat_ws(sep, str, …) 函数使用指定分隔符 sep 连接字符串。
SELECT concat(2, NULL, 22), concat_ws(' and ', 2, NULL, 22);
| concat | concat_ws |
|--------|-----------|
| 222 | 2 and 22 |
两个竖杠(||)也可以用于连接字符串,但是 NULL 参数将会返回 NULL。
SELECT 'Post'||'greSQL', 'Post'||NULL||'greSQL';
| ?column? | ?column? |
|------------|----------|
| PostgreSQL | (null) |
字符与编码
ascii(string) 函数返回第一个字符的 ASCII 码。对于 UTF8 返回 Unicode 码;对于其他多字节编码,参数必须是一个 ASCII 字符。
SELECT ascii('x');
| ascii |
|-------|
| 120 |
chr(int) 函数返回编码对应的字符。对于 UTF8,参数指定的是 Unicode 码;对于其他多字节编码,参数必须对应一个 ASCII 字符。参数不允许为 0(空字符),因为 text 数据类型不能存储空字符。
SELECT chr(120);
| chr |
|-----|
| x |
字符串长度
bit_length(string) 函数计算字符串包含的比特数;length(string)/char_length(string)/character_length(string) 函数计算字符串包含的字符数;octet_length(string) 函数计算字符串包含的字节数。
SELECT bit_length('jose'), length('jose'), octet_length('jose');
| bit_length | length | octet_length |
|------------|--------|--------------|
| 32 | 4 | 4 |
大小写转换
lower(string) 函数将字符串转换为小写形式,upper(string) 函数将字符串转换为大写形式,initcap(string) 函数将每个单词的首字母大写,其他字母小写。
SELECT lower('TOM'), upper('tom'), initcap('hi THOMAS');
| lower | upper | initcap |
|-------|-------|-----------|
| tom | TOM | Hi Thomas |
子串查找与替换
substring(string [from] [for]) 函数用于提取从位置 from 开始的 for 个字符子串,位置从 1 开始计算。substr(string, from [, count]) 的作用相同。
SELECT substring('Thomas' from 2 for 3), substr('Thomas',2, 3);
| substring | substr |
|-----------|--------|
| hom | hom |
left(str, n) 函数返回字符串左边的 n 个字符。如果 n 为负数,返回除了最后 |n| 个字符之外的所有字符。
right(str, n) 函数返回字符串右边的 n 个字符。如果 n 为负数,返回除了左边 |n| 个字符之外的字符。
SELECT left('abcde', 2), right('abcde', 2);
| left | right |
|------|-------|
| ab | de |
substring(string from pattern) 函数提取匹配 POSIX 正则表达式的子串。
substring(string from pattern for escape) 函数提取匹配 SQL 正则表达式的子串。
SELECT substring('Thomas' from '...$'), substring('Thomas' from '%#"o_a#"_' for '#');
| substring | substring |
|-----------|-----------|
| mas | oma |
regexp_match(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的第一个子串。
SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
|regexp_match|
|------------|
|{bar,beque} |
regexp_matches(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的所有子串,结果是一个集合。
SELECT regexp_matches('foobarbequebaz', 'ba.', 'g');
| regexp_matches |
|----------------|
| bar |
| baz |
position(substring in string) 返回子串的位置;strpos(string, substring) 函数的作用相同,但是参数顺序相反。
SELECT position('om' in 'Thomas'), strpos('Thomas', 'om');
| position | strpos |
|----------|--------|
| 3 | 3 |
starts_with(string, prefix) 函数判断 string 是否以 prefix 开头,如果是则返回 true;否则返回 false。
SELECT starts_with('alphabet', 'alph');
|starts_with|
|-----------|
|true |
replace(string, from, to) 函数将字符串 string 中的 from 子串替换为 to 子串;regexp_replace(string, pattern, replacement [, flags]) 函数字符串 string 中匹配 POSIX 正则表达式 pattern 的子串替换为 replacement。
SELECT replace('abcdefabcdef', 'cd', 'XX'), regexp_replace('Thomas', '.[mN]a.', 'M');
| replace | regexp_replace |
|--------------|----------------|
| abXXefabXXef | ThM |
translate(string , from , to) 函数将字符串 string 中出现在 from 中的字符串替换成 to 中相应位置的字符。如果 from 长度大于 to,在 to 中没有对应值的字符将被删除。
SELECT translate('12345', '143', 'ax');
| translate |
|-----------|
| a2x5 |
overlay(string placing substring from [for]) 函数使用 substring 覆盖字符串 string 中从 from 开始的 for 个字符。
SELECT overlay('Txxxxas' placing 'hom' from 2 for 4);
| overlay |
|---------|
| Thomas |
截断与填充
trim([leading | trailing | both] [characters] from string) 函数从字符串的开头(leading)、结尾(trailing)或者两端(both)删除由指定字符 characters(默认为空格)组成的最长子串;trim([leading | trailing | both] [from] string [, characters]) 函数的作用相同。
SELECT trim(both 'xyz' from 'yxTomxx');
| btrim |
|-------|
| Tom |
btrim(string [, characters]) 函数的作用与上面 trim 函数的 both 选项相同;ltrim(string [, characters]) 与上面 trim 函数的 leading 选项相同;rtrim(string [, characters]) 函数上面 trim 函数的 trailing 选项相同。
SELECT btrim('yxTomxx', 'xyz'), ltrim('yxTomxx', 'xyz'), rtrim('yxTomxx', 'xyz');
| btrim | ltrim | rtrim |
|-------|-------|-------|
| Tom | Tomxx | yxTom |
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 次。
SELECT lpad('hi', 5, 'xy'), rpad('hi', 5, 'xy'), repeat('Pg', 4);
| lpad | rpad | repeat |
|-------|-------|----------|
| xyxhi | hixyx | PgPgPgPg |
字符串格式化
format(formatstr , formatarg) 用于对字符串格式化,类似于 C 语言中的 sprintf 函数。
SELECT format('Hello %s, %1$s', 'World');
| format |
|--------------------|
| Hello World, World |
📝关于 format 函数的格式化参数可以参考官方文档。
MD5 值
md5(string) 函数用于返回十六进制格式的 MD5 值。
SELECT md5('abc');
| md5 |
|----------------------------------|
| 900150983cd24fb0d6963f7d28e17f72 |
字符串拆分
regexp_split_to_table(string, pattern[, flags]) 函数用于拆分字符串,使用 POSIX 正则表达式作为分隔符。函数的返回类型是 text 集合。
SELECT regexp_split_to_table('hello world', '\s+');
| regexp_split_to_table |
|-----------------------|
| hello |
| world |
split_part(string, delimiter, field) 函数使用 delimiter 拆分字符串,并返回指定项(从 1 开始计数)。
SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
| split_part |
|------------|
| def |
字符串反转
reverse(str) 函数用于将字符串反转。
SELECT reverse('上海自来水');
| reverse |
|---------|
| 水来自海上 |
📝更多字符函数可以参考官方文档。