PostgreSQL易用的开发函数

1.xmltable函数

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)

3.正则表达式函数

PostgreSQL目前支持多种POSIX风格的正则表达式函数。

4.UUID函数

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的主键策略也推荐阅读如下两篇文章：

5.string_to_table函数

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)

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)

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)

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)