《PostgreSQL 开发指南》第 16 篇 常用函数（一）

文章转载自公众号：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 |

|---------|

|   水来自海上 |

📝更多字符函数可以参考官方文档。