regexp_split_to_table，regexp_split_to_array，array，unnest 使用

实例1

select regexp_split_to_table('hello world', '\s+') ;
select regexp_split_to_array('hello world', '\s+') ;

postgres=# select regexp_split_to_table('hello world', '\s+') ;
 regexp_split_to_table
-----------------------
 hello
 world
(2 rows)
postgres=# select regexp_split_to_array('hello world', '\s+') ;
 regexp_split_to_array
-----------------------
 {hello,world}
(1 row)


nas=# drop table test;
DROP TABLE
nas=# create table test(id int);
CREATE TABLE
nas=# insert into test select n from generate_series(1,100) n;
INSERT 0 100
nas=# select id from test where id<10;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
(9 rows)

nas=# select array(select id from test where id<10);
        array        
---------------------
 {1,2,3,4,5,6,7,8,9}
(1 row)

nas=# select unnest(array(select id from test where id<10));
 unnest 
--------
      1
      2
      3
      4
      5
      6
      7
      8
      9
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

实例2

create table test(id int, info text);
insert into test values(1,'a');
insert into test values(1,'b');
select * from test;
select id, string_agg(info,';' ) from test group by id;
select id, array_agg(info ) from test group by id;
postgres=# create table test(id int, info text);
CREATE TABLE
postgres=# insert into test values(1,'a');
INSERT 0 1
postgres=# insert into test values(1,'b');
INSERT 0 1
postgres=# select * from test;
 id | info 
----+------
  1 | a
  1 | b
  (2 rows)
postgres=#  select id, string_agg(info,';' ) from test group by id;
 id | string_agg 
----+------------
  1 | a;b
(1 row)

postgres=# select id, array_agg(info ) from test group by id;
 id | array_agg 
----+-----------
  1 | {a,b}
(1 row)


create table test2(id int, info text);
insert into test2 values(1,'a;b');
select * from test2;
select id, regexp_split_to_table(info,';') from test2;
postgres=# create table test2(id int, info text);
CREATE TABLE
postgres=# insert into test2 values(1,'a;b');
INSERT 0 1
postgres=# select * from test2;
 id | info 
----+------
  1 | a;b
(1 row)

postgres=# select id, regexp_split_to_table(info,';') from test2;
 id | regexp_split_to_table 
----+-----------------------
  1 | a
  1 | b
(2 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51