postgresql游标数据装到数组中

由于在postgresql中获取数据集通常是使用游标cursor接收，但游标处理数据很不方便，并且耗时比较多，因此将游标数据保存到数组中是比较好的一个方法。

--创建数组类型
create type array_rest_day as 
(
	id int4,
	start_time timestamp,
	end_time timestamp
); 

--创建函数
create or replace function count_rest_day(end_time timestamp,start_time timestamp)
returns interval as $hour$
declare
	cursor_name cursor for
	(
	select * from t_rest_day t where t.start_time between start_date and end_date
	union 
	select * from t_rest_day t where t.end_time between start_date and end_date
	order by start_time
	);
	ref_datas array_rest_day[];
	row_num int;
	rest_cost interval := '0 day'
	...
begin 
	for rd in cursor_name loop  
    ref_datas[row_num]=row(rd.id, rd.start_time, rd.end_time);  
    row_num=row_num+1;  
	end loop;  
	if(ref_datas is null || array_length(ref_datas, 1) = 0) then 
		return end_time - start_time;
	end if;
	if(age(min_time,start_time) && age(end_time,max_time)) then 
		for i in 0..array_length(ref_datas, 1)-1 loop
			rest_cost = rest_cost + (ref_datas[i].end_time - ref_datas[i].start_time);
		end loop;
	end if;
	...
  return end_time-start_time-rest_cost;
end 
$hour$ LANGUAGE plpgsql;

--测试
select count_rest_day('2018-01-01 10:00:00','2018-01-02 09:00:00');

参考文献：http://625514418-qq-com.iteye.com/blog/1677384