PG中 generate_series函数的使用

generate_series函数的使用

• 语法
• 实例
• 实例2

语法

实例

• 一次使用一遍

select generate_series(3,5);

结果如下：

• 一次使用两遍
  • 间距相同

  select generate_series(3,5), generate_series(4,6);
  

  结果：
  
  • 间距不同

  select generate_series(3,5), generate_series(4,7);
  

  结果：
  

实例2

CREATE OR REPLACE FUNCTION load_data_to_tbl_operlog(row_num int) RETURNS INTEGER AS
$BODY$
declare
	max_operlog_id bigint;
BEGIN
drop SEQUENCE if exists tbl_operlog_id_seq;
select max(operlog_id) INTO max_operlog_id  from tbl_operlog;
max_operlog_id :=COALESCE(max_operlog_id ,0)+1;
execute 'create SEQUENCE if not exists tbl_operlog_id_seq   start with '||max_operlog_id ;
insert into tbl_operlog 
select 
	nextval('tbl_operlog_id_seq'), 
	n||'imos_name', 
	now(), 
	'127.0.0.1' as ip_address,
	random()*10 as service_type,
	n||'oper_object' as oper_object,
	random()*10 as oper_type,
	random()*10 as oper_result,
	n||'description',
	n||'failure_cause'
from  generate_series(1,row_num) n;
          RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED