PostgreSQL的generate_series函数应用

xuxiaohui9216

　　一、简介
　　PostgreSQL 中有一个很有用处的内置函数generate_series，可以按不同的规则产生一系列的填充数据。
　　二、语法

函数参数类型返回类型描述

generate_series(start, stop)

int 或 bigint

setof int 或 setof bigint(与参数类型相同)

生成一个数值序列，从start 到 stop，步进为一


generate_series(start, stop, step)

int 或 bigint

setof int 或 setof bigint(与参数类型相同)
生成一个数值序列，从start 到 stop，步进为step


generate_series(start, stop, step_interval)
timestamp or timestamp with time zone

timestamp 或 timestamp with time zone(same as argument type)
生成一个数值序列，从start 到 stop，步进为step
　　三、实例

　　3.1) int 类型
　　a. 不写步进时默认为1



david=# select generate_series(1, 10);
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
(10 rows)
david=#
　　b. 设置步进



david=# select generate_series(1, 10, 3);
generate_series
-----------------
1
4
7
10
(4 rows)
david=#
　　c. 如果step 是正数，而start 大于stop，那么返回零行。相反，如果step 是负数，start 小于stop，则返回零行。如果是NULL 输入，也产生零行。step 为零则是一个错误。



david=# select generate_series(5,1);
generate_series
-----------------
(0 rows)
david=#
　　NULL inputs



david=# select generate_series(5,null);
generate_series
-----------------
(0 rows)
david=#
　　step 为零



david=# select generate_series(5,1,0);
ERROR:  step size cannot equal zero
david=#
　　start 大于stop，step 是负数



david=# select generate_series(5,1,-1);
generate_series
-----------------
5
4
3
2
1
(5 rows)
david=#
　　3.2) 时间类型



david=# select generate_series(now(), now() + '7 days', '1 day');
generate_series        
-------------------------------
2013-04-03 14:22:26.391852+08
2013-04-04 14:22:26.391852+08
2013-04-05 14:22:26.391852+08
2013-04-06 14:22:26.391852+08
2013-04-07 14:22:26.391852+08
2013-04-08 14:22:26.391852+08
2013-04-09 14:22:26.391852+08
2013-04-10 14:22:26.391852+08
(8 rows)
david=#


david=# select generate_series(to_date('20130403','yyyymmdd'), to_date('20130404','yyyymmdd'), '3 hours');  
generate_series     
------------------------
2013-04-03 00:00:00+08
2013-04-03 03:00:00+08
2013-04-03 06:00:00+08
2013-04-03 09:00:00+08
2013-04-03 12:00:00+08
2013-04-03 15:00:00+08
2013-04-03 18:00:00+08
2013-04-03 21:00:00+08
2013-04-04 00:00:00+08
(9 rows)
david=#
　　3.3) IP类型
　　a. 建表



david=# create table tbl_david(id int, ip_start inet, ip_stop inet);
CREATE TABLE
david=#
　　b. 插入数据



david=# insert into tbl_david values (1, '192.168.1.6', '192.168.1.10');   
INSERT 0 1
david=# insert into tbl_david values (2, '192.168.2.16', '192.168.2.20');  
INSERT 0 1
david=# insert into tbl_david values (3, '192.168.3.116', '192.168.3.120');
INSERT 0 1
david=#
　　c. 查看数据



david=# select * from tbl_david ;
id |   ip_start    |    ip_stop   
----+---------------+---------------
1 | 192.168.1.6   | 192.168.1.10
2 | 192.168.2.16  | 192.168.2.20
3 | 192.168.3.116 | 192.168.3.120
(3 rows)
david=#
　　d. generate_series 生成序列



david=# select id, generate_series(0, ip_stop-ip_start)+ip_start as ip_new from tbl_david ;
id |    ip_new     
----+---------------
1 | 192.168.1.6
1 | 192.168.1.7
1 | 192.168.1.8
1 | 192.168.1.9
1 | 192.168.1.10
2 | 192.168.2.16
2 | 192.168.2.17
2 | 192.168.2.18
2 | 192.168.2.19
2 | 192.168.2.20
3 | 192.168.3.116
3 | 192.168.3.117
3 | 192.168.3.118
3 | 192.168.3.119
3 | 192.168.3.120
(15 rows)
david=#
　　四、总结
　　PostgreSQL的generate_series函数对生成测试数据，批量更新一定规则的数据有比较多的应用场景，使用得当可提升开发效率，另外IP的序列生成也是PG的一个亮点。
　　五、参考

• PostgreSQL官方文档：http://www.postgresql.org/docs/9.2/static/functions-srf.html
  
• kenyon的个人页面：http://my.oschina.net/Kenyon/blog/75099