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樂知樂享交流群，歡迎關注文章的小伙伴加微信進群吹牛嘮嗑，交流技術。
