Unnest函数的几个小特性

原创多米爸比 2022-11-26

1483

本文将介绍unnest函数的如下几个小特性：

使用support属性准确预估行
将字符串转化为表
基于ordinality属性生成行号
巧用variadic可变参数

使用support属性准确预估行

PostgreSQL 12对函数新增了support属性，函数使用support属性之后，优化器可以准确的预估行。

PostgreSQL 12及以上的版本，可以看到下面预估的行数是比较准确的。

postgres=# explain select * from unnest(array[1,2,3]);
                        QUERY PLAN                         
-----------------------------------------------------------
 Function Scan on unnest  (cost=0.00..0.03 rows=3 width=4)
(1 row)

我们可以查看函数的定义，PostgreSQL 12里unnest函数增加了support属性。

postgres=# \sf unnest(anyarray)
CREATE OR REPLACE FUNCTION pg_catalog.unnest(anyarray)
 RETURNS SETOF anyelement
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT ROWS 100 SUPPORT array_unnest_support
AS $function$array_unnest$function$

将字符串转化为表

下面的示例使用string_to_array函数结合unnest函数将字符串转换为表。

postgres=# select unnest(string_to_array('a,b,c,d',','));
 unnest 
--------
 a
 b
 c
 d
(4 rows)

在PostgreSQL 14及以上的版本，可以使用string_to_table函数进行简化。

postgres=# select string_to_table('a,b,c,d',',');
 string_to_table 
-----------------
 a
 b
 c
 d
(4 rows)

string_to_table()等价于unnest(string_to_array())。

基于ordinality属性生成行号

继续沿用上面的示例，unnest函数后面使用with ordinality属性可以比较方便的生成行号。

postgres=# select f1,f2 from 
            unnest(string_to_array('a,b,c,d',',')) 
            with ordinality t(f2,f1);
 f1 | f2 
----+----
  1 | a
  2 | b
  3 | c
  4 | d
(4 rows)

巧用variadic可变参数

下面的示例，有一张航天工程的表结构如下

create table projects(
project_name varchar primary key, 
spaceman varchar, 
degree varchar
);

构造两条数据：

insert into projects(project_name, spaceman, degree)
values 
('神舟N号',  
'张三
李四
王五', 
'本科
本科
博士'),
('月盾计划',  
'沈腾
马丽', 
'本科
硕士');

打印表数据如下，每行为一次航天工程，第二列包含所有航天员的名称，第三行为航天员对应的学位。

postgres=# select * from projects ;
 project_name | spaceman | degree 
--------------+----------+--------
 神舟N号      | 张三    +| 本科  +
              | 李四    +| 本科  +
              | 王五     | 博士
 月盾计划     | 沈腾    +| 本科  +
              | 马丽     | 硕士
(2 rows)

PostgreSQL里可以非常容易对第二列和第三列进行分解：

postgres=# select p.project_name, e.name, e.degree
             from projects as p,
                  unnest(string_to_array(spaceman, e'\n'),
                         string_to_array(degree, e'\n')) as e(name, degree);
 project_name | name | degree 
--------------+------+--------
 神舟N号      | 张三 | 本科
 神舟N号      | 李四 | 本科
 神舟N号      | 王五 | 博士
 月盾计划     | 沈腾 | 本科
 月盾计划     | 马丽 | 硕士
(5 rows)