root/trunk/table_growth/otools.summarize_table_growth.sql

Revision 17, 1.4 kB (checked in by robert, 6 years ago)

actual size need never be null, afaict

Line 
1 create or replace function otools.summarize_table_growth() 
2 returns void
3 as $$
4 declare
5     v_sql text;
6 begin
7
8 -- Daily summarization
9 IF to_char(current_date,'dd') <> '01' THEN
10     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time)
11     select a.table_owner, a.schema_name, a.table_name, a.actual_size, a.actual_size-b.actual_size AS table_growth, 1, a.capture_time
12     from otools.table_growth a
13         left join otools.table_growth b
14             on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name and b.capture_time = current_date -1)
15     where
16         a.sum_flag=0 and a.capture_time = current_date;
17     -- now remove older rows
18     delete from otools.table_growth where sum_flag = 0;
19 END IF;
20
21 -- Monthly summarization
22 IF to_char(current_date,'dd') = '01' THEN
23     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time)
24     select a.table_owner, a.schema_name, a.table_name, actual_size, sum(growth_size), 2, (current_date - '1 month'::interval)
25     from otools.table_growth a
26     where sum_flag=1 and capture_time between (current_date - '1 month'::interval) and current_date
27     group by table_owner, schema_name, table_name, actual_size;
28     -- now remove older rows
29     delete from otools.table_growth where sum_flag = 1;
30 END IF;
31
32 end
33 $$ language plpgsql;
Note: See TracBrowser for help on using the browser.