쓸일이 있어서 만들어 봤습니다.
상기님의 재귀함수 강좌(http://database.sarang.net/?inc=read&aid=4585&criteria=pgsql&subcrit=tutorials&id=&limit=20&keyword=&page=2)
를 보고 수정해서 만들어 보았습니다.
필요하신분들 계실거 같아서요.
CREATE OR REPLACE FUNCTION "public"."cdate" (date,
date,interval) RETURNS SETOF "pg_catalog"."record" AS
$body$
DECLARE
node1 record;
subnode record;
BEGIN
FOR node1 IN SELECT to_date($1 + $3,'YYYY-MM-DD') as cdate
where to_date($1 + $3,'YYYY-MM-DD')<= $2 LOOP
RETURN NEXT node1;
FOR subnode IN SELECT * FROM cdate(node1.cdate, $2,$3) as
t(cdate date) LOOP
RETURN NEXT subnode;
END LOOP;
END LOOP;
RETURN;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
INVOKER;
사용법
SELECT * FROM cdate('2005-12-31'::date,'2006-01-31',interval'1
day') as t(cdate date)
cdate
------------
2006-01-01
2006-01-02
2006-01-03
.
.
.
2006-01-29
2006-01-30
2006-01-31
SELECT to_char(cdate,'YYYY-MM') FROM
cdate('2005-12-31'::date,'2006-12-31',interval' 1 month ') as
t(cdate date);
to_char
---------
2006-01
2006-02
2006-03
2006-04
2006-05
2006-06
2006-07
2006-08
2006-09
2006-10
2006-11
2006-12
고운하루 되십시요
|