root/sql/sprocs/stratcon.fetch_dataset.sql

Revision dfd06d8bc70524168b54bb26477b5f0a3f3a38fe, 4.2 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

fix up things to work

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