[PGS9.2] 임의의 Sequence, Series, Identity를 row로 생성하기

/********************************************************************************************
-- Title : [PGS9.2] 임의의 Sequence, Series, Identity를 row로 생성하기
-- Reference : http://www.postgresql.org/docs/9.2/static/functions-srf.html
-- Key word : generate_series generate series sequence identity level 더미 dummy
                    임의 데이터 생성 샘플 데이터 추가 sample data
********************************************************************************************/

-- 테이블 생성

-- drop table ttt_srl;

create table ttt_srl

( a int, b int);

insert into ttt_srl values (1,1);

insert into ttt_srl values (2,2);

insert into ttt_srl values (3,3);

select * from ttt_srl;

-- generate_series함수

select generate_series(1,5);

select generate_series(0,500,100);

select generate_series(500,0,-100);

select * from generate_series(1,1000, 100);

-- 활용 

select * 

from ttt_srl

cross join 

(select generate_series(1,10) "pivot") b

select current_date + "series"."index" as "dates", "series"."index"

from generate_series(0,365,7) as "series"("index");

select date('2013-01-01') + (to_char(a,'99')||' month')::interval as date

     , to_char(a,'99')||' month', a

from generate_series(0,19) as a;

select current_date + s.a as dates 

from generate_series(0,14,7) as s(a);

select * 

from generate_series('2008-03-01 00:00'::timestamp

                    ,'2008-03-04 12:00', '10 hours');