root/sql/sprocs/noit.fetch_dataset.sql

Revision b57ec8315f866c742be1da0a080ee0c836ff02a1, 3.9 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

noit functions related to fetch data

  • Property mode set to 100644
Line 
1 -- Function: noit.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean)
2
3 CREATE OR REPLACE FUNCTION noit.fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)
4   RETURNS SETOF noit.metric_numeric_rollup_5m AS
5 $BODY$declare
6   v_sid int;
7   v_record noit.metric_numeric_rollup_5m%rowtype;
8 begin
9   select sid into v_sid from noit.map_uuid_to_sid where id = in_uuid;
10   if not found then
11     return;
12   end if;
13
14     for v_record in  select sid, name, rollup_time, count_rows, avg_value, counter_dev from noit._fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive) loop
15     return next v_record;
16     end loop;
17
18 --  return query select sid, name, rollup_time, count_rows, avg_value from noit._fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive);
19   return;
20 end
21 $BODY$
22   LANGUAGE 'plpgsql' VOLATILE;
23
24 CREATE OR REPLACE FUNCTION noit._fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)
25   RETURNS SETOF noit.metric_numeric_rollup_5m AS
26 $BODY$declare
27   v_sql text;
28   v_sid int;
29   v_target record;
30   v_interval numeric;
31   v_start_adj timestamptz;
32   v_end_adj timestamptz;
33   v_l_rollup_row noit.metric_numeric_rollup_5m%rowtype;
34   v_rollup_row noit.metric_numeric_rollup_5m%rowtype;
35   v_r_rollup_row noit.metric_numeric_rollup_5m%rowtype;
36 begin
37
38   -- Map out uuid to an sid.
39   v_sid := in_sid;
40
41   select * into v_target from noit.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
42
43   if not found then
44     raise exception 'no target table';
45     return;
46   end if;
47
48   select 'epoch'::timestamp +
49          ((floor(extract('epoch' from in_start_time) /
50                  extract('epoch' from v_target.period)) *
51            extract('epoch' from v_target.period)) || ' seconds') ::interval
52     into v_start_adj;
53
54   select 'epoch'::timestamp +
55          ((floor(extract('epoch' from in_end_time) /
56                  extract('epoch' from v_target.period)) *
57            extract('epoch' from v_target.period)) || ' seconds') ::interval
58     into v_end_adj;
59
60   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
61            's.rollup_time, d.count_rows, d.avg_value, d.counter_dev ' ||
62            ' from ' ||
63            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
64                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
65                        ' as rollup_time' ||
66              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
67            'left join ' ||
68            '(select * from noit.' || v_target.tablename ||
69            ' where sid = ' || v_sid ||
70              ' and name = ' || quote_literal(in_name) ||
71              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
72                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
73            ' using(rollup_time)';
74
75   for v_rollup_row in execute v_sql loop
76     if derive is true then
77       v_r_rollup_row := v_rollup_row;
78       if v_l_rollup_row.count_rows is not null and
79          v_rollup_row.count_rows is not null then
80         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
81         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
82         v_r_rollup_row.avg_value :=
83           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
84       else
85         v_r_rollup_row.count_rows = NULL;
86         v_r_rollup_row.avg_value = NULL;
87        
88       end if;
89     else
90       v_r_rollup_row := v_rollup_row;
91     end if;
92     return next v_r_rollup_row;
93     v_l_rollup_row := v_rollup_row;
94   end loop;
95   return;
96 end
97 $BODY$
98   LANGUAGE 'plpgsql' VOLATILE;
Note: See TracBrowser for help on using the browser.