/*
* Param One:开始时间
* Param Two:结束时间
* 功能 : 返回时间段之间的每月最后一天
*/
drop function if exists getmonths(text,text);
create or replace function getmonths(text,text)
returns setof int as $body$
declare
count int := 0;
r int;
top date := ($1||'01')::date;
down date:= ($2||'01')::date;
begin
if down < top then return next replace(last_day(top),'-','')::int; end if;
count = extract(month from age(down,top))+extract(year from age(down,top))*12;
for i in 0..count loop
select replace(last_day(((top + (i||' month')::interval))::date),'-','')::int into r;
if r::text::date + 1 >= current_date then
r = replace(current_date-1,'-','')::int;
return next r;
exit;
end if;
return next r;
end loop;
end $body$ language plpgsql returns null on null input;
select getmonths('201101','201809');