PostgreSQL里有非常丰富的系统功能函数，本文着重介绍一些与开发相关的系统函数。
- xmltable函数
- SQL/JSON path函数
- 正则表达式函数
- UUID函数
- string_to_table函数
- date_bin函数
XMLTABLE是SQL/XML标准定义构造XML格式数据的函数，允许将XML格式数据当成TABLE一样与其它数据表进行关联查询，并且该函数对XML数据的处理解析比客户端处理性能更高。
下面构造一个简单的XML类型数据表，作为数据源进行演示。
create table test_people as select
xml $$
<people>
<person>
<first_name>Pavel</first_name>
<last_name>Feng</last_name>
<nick>john</nick>
</person>
<person>
<first_name>Jerome</first_name>
<last_name>Peng</last_name>
</person>
</people>
$$ as xml_data;
然后我们对XML数据按照TABLE方式进行列查询：
select decoded.*
from
test_people,
xmltable(
'//people/person'
passing xml_data
columns
first_name text,
last_name text,
nick text
) as decoded;
上面语句查询结果如下：
first_name | last_name | nick
------------+-----------+------
Pavel | Feng | john
Jerome | Peng |
(2 rows)
我们可以对XML数据里的节点名进行重命名：
select decoded.*
from
test_people,
xmltable(
'//people/person'
passing xml_data
columns
first_name text,
last_name text,
nick_name text path 'nick'
) as decoded;
查询结果如下，返回表字段时nick映射为nick_name：
first_name | last_name | nick_name
------------+-----------+-----------
Pavel | Feng | john
Jerome | Peng |
(2 rows)
同时也可以对缺失的子节点项设置默认值：
select decoded.*
from
test_people,
xmltable(
'//people/person'
passing xml_data
columns
first_name text,
last_name text,
nick text DEFAULT '---'
) as decoded;
查询结果如下：nick节点缺失时使用默认值“—”替代。
first_name | last_name | nick
------------+-----------+------
Pavel | Feng | john
Jerome | Peng | ---
(2 rows)
也可以对结果集添加首列行号：
select decoded.*
from
test_people,
xmltable(
'//people/person'
passing xml_data
columns
id for ordinality,
first_name text,
last_name text,
nick text DEFAULT '---'
) as decoded;
查询结果如下：id列为添加的数据行号
id | first_name | last_name | nick
----+------------+-----------+------
1 | Pavel | Feng | john
2 | Jerome | Peng | ---
(2 rows)
还可以使用XPATH函数进行处理，比如组合first_name与last_name：
select decoded.*
from
test_people,
xmltable(
'//people/person'
passing xml_data
columns
id for ordinality,
full_name text PATH 'concat(first_name, " ", last_name)',
nick text DEFAULT '---'
) as decoded;
查询结果如下：使用concat函数对first_name与last_name进行组合。
id | full_name | nick
----+-------------+------
1 | Pavel Feng | john
2 | Jerome Peng | ---
(2 rows)
JSON path是与开发人员高度相关性的一个特性，PostgreSQL提供了大量的新功能函数来更快捷地解析JSON类型。
SQL/JSON path 函数表达式使用了JavaScript的一些语法，如下:
- 点号.表示引用Json数据的元素
- 方括号[]表示引用数组元素
- Json 数据中的数组元素下标从0开始
SQL/JSON path 函数表达式的变量，如下:
- $符号表示要查询的Json文本的变量
- $varname表示指定变量
- @ 指在filter表达式中表示当前路径元素的变量
创建以下测试表并插入一条 JSON测试数据，准备如下：
CREATE TABLE T_JSONPATH (a jsonb);
INSERT INTO T_JSONPATH (a) VALUES ('
{ "gpsname": "postgres",
"track" :
{
"segments" : [
{ "location": [ 49.773, 15.2104 ],
"start time": "2020-05-11 10:05:14",
"HR": 73
},
{ "location": [ 49.776, 15.4125 ],
"start time": "2020-06-21 10:39:21",
"HR": 130
} ]
}
}');
如果通过JSON操作符查询JSON数据元素值，如下：
postgres=# SELECT a ->> 'gpsname' FROM T_JSONPATH;
?column?
----------
postgres
(1 row)
使用SQL/JSON path函数表达式进行查询
postgres=# SELECT jsonb_path_query(a,'$.gpsname') FROM T_JSONPATH;
jsonb_path_query
------------------
"postgres"
(1 row)
jsonb_path_query函数是JSON path最常用函数。
如果JSON数据涉及较多层级，这时JSON path的函数表达式就更加易用，比如查询表T_JSONPATH的track.segments下一层级的元素。
postgres=# SELECT jsonb_path_query(a,'$.track.segments[1].HR') FROM T_JSONPATH;
jsonb_path_query
------------------
130
(1 row)
除此之外，还可使用jsonb_path_exists函数判断是否存在指定Json路径，语法如下：
postgres=# SELECT jsonb_path_exists(a,'$.track.segments.HR') FROM T_JSONPATH;
jsonb_path_exists
-------------------
t
(1 row)
PostgreSQL目前支持多种POSIX风格的正则表达式函数。
- substring(string text FROM pattern text)
提取匹配正则表达式的第一个子串。 - regexp_match(string text, pattern text [, flags text])
以字符串数组返回字符串与正则表达式第一个匹配的子串。 - regexp_matches(string text, pattern text [, flags text])
以字符串数组集合返回字符串与正则表达式第一个匹配的子串，第三个可选参数使用g标志则匹配所有。 - regexp_replace(string text, pattern text, replacement text [, flags text])
替换字符串中第一个正则表达式匹配的子串，第四个可选参数使用g标志则替换所有。 - regexp_split_to_array(string text, pattern text [, flags text])
使用正则表达式将字符串拆解为字符串数组。 - regexp_split_to_table( string text, pattern text [, flags text])
使用正则表达式将字符串拆解为字符串集合。 - regexp_count(string text, pattern text [, start integer [, flags text ]])
统计字符串匹配某个子串的次数，第三个可选参数可以指定从第N个字符的开始，第四个可选参数允许设置一些标志位。 - regexp_instr(string text, pattern text [, start integer [, N integer [, endoption integer
[, flags text [, subexpr integer ] ] ] ] ])
返回正则表达式第N次匹配的位置。 - regexp_like(string text, pattern text [, flags text])
检测字符串是否正则模糊匹配，第三个可选参数使用i标志可以忽略大小写。 - regexp_substr(string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ])
返回正则表达式第N次匹配的子串。
PostgreSQL 13之前不提供生成UUID的内置函数，如果需要使用UUID数据类型，需要加载外部uuid_ossp或pgcrypto扩展，才能生成UUID数据。从PostgreSQL 13开始可以直接使用系统函数gen_random_uuid()函数，示例如下：
postgres=# SELECT gen_random_uuid();
gen_random_uuid
--------------------------------------
960d6103-090e-472e-901e-daac7b73a3a3
(1 row)
PostgreSQL的主键策略也推荐阅读如下两篇文章：
string_to_table函数可以对将字符串按分隔符拆分为数据行，它与string_to_array函数类似，等价于unnest(string_to_array())，并且性能也更好。
示例如下：
postgres=# select string_to_table('foo,bar,baz',',');
string_to_table
-----------------
foo
bar
baz
(3 rows)
第一个参数为待拆分的字符串，如果第二个参数分隔符为NULL，则字符串的每个字符将作为独立的一行，如下：
postgres=# select string_to_table('abcdefg',null);
string_to_table
-----------------
a
b
c
d
e
f
g
(7 rows)
如果分隔符为空串，则整个字符串将作为一行，如下：
postgres=# select string_to_table('abcdefg','');
string_to_table
-----------------
abcdefg
(1 row)
如果第三个参数不为NULL，则匹配的子串将替换为NULL，如下：
postgres=# select string_to_table('ab,cd,ef,gh',',','cd');
string_to_table
-----------------
ab
ef
gh
(4 rows)
注意string_to_table函数不适用于解析CSV格式字符串，如下：
postgres=# select string_to_table('foo,bar,baz,"baz,boo"',',');
string_to_table
-----------------
foo
bar
baz
"baz
boo"
(5 rows)
date_bin函数可以将指定的时间戳强制截断到最接近指定时间间隔的开头。date_bin函数功能类似date_trunc，但date_bin函数可以截断为任意时间间隔，不要求间隔只能是一个时间单位。
date_trunc函数按时间单位进行截断的示例如下：
postgres=# select 'untruncated' as spec, now()
union all
select spec, date_trunc(spec, now())
from string_to_table('microseconds,milliseconds,second,minute,hour,day,week,month,quarter,year,decade,century,millennium',',') as u(spec);
spec | now
--------------+-------------------------------
untruncated | 2022-12-08 15:25:30.891473+08
microseconds | 2022-12-08 15:25:30.891473+08
milliseconds | 2022-12-08 15:25:30.891+08
second | 2022-12-08 15:25:30+08
minute | 2022-12-08 15:25:00+08
hour | 2022-12-08 15:00:00+08
day | 2022-12-08 00:00:00+08
week | 2022-12-05 00:00:00+08
month | 2022-12-01 00:00:00+08
quarter | 2022-10-01 00:00:00+08
year | 2022-01-01 00:00:00+08
decade | 2020-01-01 00:00:00+08
century | 2001-01-01 00:00:00+08
millennium | 2001-01-01 00:00:00+08
(14 rows)
date_bin函数的示例如下：
postgres=# select date_bin('15 minutes', timestamp '2021-05-12 13:41:23', timestamp '2001-01-01');
date_bin
---------------------
2021-05-12 13:30:00
(1 row)
date_bin函数的第一个参数为时间间隔，例如15 minutes(minutes也可简写为min或m)表示以15分钟为间隔，间隔有四个时间点：0分钟、15分钟、30分钟和45分钟。
date_bin函数的第二个参数为要处理的时间戳，第三个参数如果有时间部分，则时间部分将作为偏移量添加到结果的时间部分。
postgres=# select date_bin('15 minutes', timestamp '2021-05-12 13:41:23', timestamp '2001-01-01 00:05:01');
date_bin
---------------------
2021-05-12 13:35:01
(1 row)
按间隔截断的2021-05-12 13:30:00将添加一个偏移量05:01，结果是：2021-05-12 13:35:01。
其实还有大量与开发相关的函数并未介绍全，例如丰富的窗口函数、数学函数(最大公约数gcd函数，最小公倍数lcm函数，阶乘factorial函数)等。
最后推荐下面这个网址，方便进行函数检索及示例学习
保持联系
现组建了一个PG乐知乐享交流群，欢迎关注文章的小伙伴加微信进群吹牛唠嗑，交流技术。
