------------------------------------------------
-- Å×ÀÌºí »ý¼º
------------------------------------------------
-- drop table public.df_m_result;
-- drop table public.df_m_date;
create table public.df_m_result
( ident serial 
, list varchar(1000)
);

create table public.df_m_date
( ident serial
, list varchar(1000)
);

------------------------------------------------
-- È®ÀÎ
------------------------------------------------
select * from public.df_m_date;
select * from public.df_m_result;

------------------------------------------------
-- ¼öÇà ÆÄÀÏ »ý¼º
------------------------------------------------
vi /home/postgres/META/exec_df_m.sh
#!/bin/sh

rm -rf /home/postgres/META/*.log
rm -rf /home/postgres/META/df_m_date.txt
rm -rf /home/postgres/META/df_m_result.txt

dd=`date '+%Y%m%d'`
echo `date`

echo "$dd||This command [df-m] has been executed. " > /home/postgres/META/df_m_date.txt
df -m > /home/postgres/META/df_m_result.txt

/home/postgres/pgsql/bin/psql -c "truncate table public.df_m_date;"
/home/postgres/pgsql/bin/psql -c "COPY public.df_m_date (list) FROM '/home/postgres/META/df_m_date.txt' with delimiter E'\t';"

/home/postgres/pgsql/bin/psql -c "truncate table public.df_m_result;"
/home/postgres/pgsql/bin/psql -c "COPY public.df_m_result (list) FROM '/home/postgres/META/df_m_result.txt' with delimiter E'\t';"

------------------------------------------------
-- °ü¸® Å×ÀÌºí »ý¼º
------------------------------------------------
-- drop table public.JOB_disk_usage;
create table public.JOB_disk_usage
( grt_dt char(8) not null
, seq int not null
, filesystem varchar(50) null
, blocks int null
, used int null
, available int not null
, used_pct varchar(5) null
, mounted varchar(50) not null
, constraint pk_job_disk_usage primary key(grt_dt, seq)
);

-- drop table public.JOB_disk_usage_log;
create table public.JOB_disk_usage_log
( grt_dt char(8) not null
, cntnt varchar(1000) null
, constraint pk_job_disk_usage_log primary key(grt_dt)
);

------------------------------------------------
-- disk_usage Å×À̺í ÀûÀç
------------------------------------------------
$ vi /home/postgres/META/ins_disk_usage.sh
#!/bin/sh

dd=`date '+%Y%m%d'`

echo `date`

echo "$dd||This command [df-m] has been executed. " > /home/postgres/META/df_m_date.txt
df -m > /home/postgres/META/df_m_result.txt

/home/postgres/pgsql/bin/psql -c "
    insert into public.JOB_disk_usage_log
    select left(list, 8) "grt_dt"
         , right(list, length(list) - strpos(list, '||') - 1 ) "cntnt"
    from public.df_m_date
    where left(list, 8) = REPLACE(current_date::char(10),'-','');
"

/home/postgres/pgsql/bin/psql -c "
    insert into public.JOB_disk_usage
    SELECT REPLACE(current_date::char(10),'-','') GRT_DT
         , a.ident - 1
         , a.filesystem
         , cast(a.block as int)
         , cast(a.used as int)
         , cast(a.available as int)
         , substring(a.list from 1 for strpos(a.list,' ')-1) use_pct , ltrim(substring(a.list from strpos(a.list,' ')+1 for 1000)) mounted
    FROM (
           SELECT a.ident , a.filesystem, a.block, a.used, substring(a.list from 1 for strpos(a.list,' ')-1) available , ltrim(substring(a.list from strpos(a.list,' ')+1 for 1000)) list, grt_dt
           FROM (
                  SELECT a.ident , a.filesystem ,a.block, substring(a.list from 1 for strpos(a.list,' ')-1) used , ltrim(substring(a.list from strpos(a.list,' ')+1 for 1000)) list, grt_dt
                  FROM (
                         SELECT a.ident , a.filesystem , substring(a.list from 1 for strpos(a.list,' ')-1) block , ltrim(substring(a.list from strpos(a.list,' ')+1 for 1000)) list, grt_dt
                         FROM (
                                SELECT a.ident , substring(a.list from 1 for strpos(a.list,' ')-1) FILESYSTEM , ltrim(substring(a.list from strpos(a.list,' ')+1 for 1000)) list, grt_dt
                                FROM(
                                      SELECT ident , CASE WHEN list2 IS NULL THEN list1
                                                          ELSE list1 || list2
                                                     END list
                                                   , grt_dt
                                      FROM ( SELECT a.ident ident, a.list list1 , b.list list2
                                             FROM public.df_m_result a
                                             LEFT JOIN (SELECT *
                                                        FROM public.df_m_result
                                                        WHERE list LIKE ' %') b
                                             ON a.ident+1 = b.ident
                                             WHERE a.list NOT LIKE ' %'
                                           ) a
                                      cross join
                                           (
                                             select left(list, 8) "grt_dt"
                                             from public.df_m_date
                                           ) b
                                     ) a
                              ) a
                       ) a
                ) a
         ) a
    where filesystem <> 'Filesystem'
    and grt_dt = REPLACE(current_date::char(10),'-','');

"

------------------------------------------------
-- CRONTAB µî·Ï
------------------------------------------------
ddi# su - oracle
$ crontab -e
0 8 * * * /home/postgres/META/exec_df_m.sh &> exec_df_m.log
5 8 * * * /home/postgres/META/ins_disk_usage.sh &> /home/postgres/META/ins_disk_usage.log