PG中 generate_series函数的使用

generate_series函数的使用

语法

实例

• 一次使用一遍

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