root/sql/sprocs/stratcon.window_robust_derive.sql

Revision 27d71aae523d1e2c256d396c8a0582f8a0052874, 2.7 kB (checked in by Robert Treat <robert@omniti.com>, 4 years ago)

need to return a return type of a type that exists

  • Property mode set to 100644
Line 
1 -- Function: stratcon.window_robust_derive(timestamp with time zone)
2
3 DROP TYPE IF EXISTS stratcon.metric_numeric_rollup_segment;
4 CREATE TYPE stratcon.metric_numeric_rollup_segment AS (sid integer,  name text, rollup_time timestamp with time zone, count_rows integer, avg_value numeric, counter_dev numeric);     
5
6
7 CREATE OR REPLACE FUNCTION stratcon.window_robust_derive(in_start_time timestamp with time zone)
8   RETURNS SETOF stratcon.metric_numeric_rollup_segment AS
9 $BODY$
10 declare
11   rec stratcon.metric_numeric_rollup_segment%rowtype;
12   r record;
13   rise numeric;
14   last_row_whence timestamp;
15   last_value numeric;
16   run numeric;
17 begin
18
19    rec.sid := null;
20    rec.name := null;
21    rec.count_rows := 0;
22    rec.avg_value := 0;
23    rise := 0;
24    run := 0;
25    rec.rollup_time = in_start_time;
26    for r in SELECT sid, name, whence,
27                    (whence > in_start_time - '5 minutes'::interval) as in_window,
28                    value
29               FROM metric_numeric_archive
30              WHERE whence <= in_start_time
31                AND whence > in_start_time - ('5 minutes'::interval * 2)
32           order BY sid,name,whence
33   loop
34   if (rec.sid is not null and rec.name is not null) and
35      (rec.sid <> r.sid or rec.name <> r.name) then
36      if rec.count_rows > 0 then
37        rec.avg_value := rec.avg_value / rec.count_rows;
38        if run is not null and run > 0 then
39          rec.counter_dev := rise/run;
40        end if;
41        return next rec;
42      end if;
43      rec.avg_value := 0;
44      rec.count_rows := 0;
45      rec.counter_dev := null;
46      rise := 0;
47      run := 0;
48      last_value := null;
49      last_row_whence := null;
50   end if;
51   rec.sid := r.sid;
52   rec.name := r.name;
53   if r.in_window then
54     if r.value is not null then
55       rec.count_rows := rec.count_rows + 1;
56       rec.avg_value := rec.avg_value + coalesce(r.value,0);
57       if     last_row_whence is not null
58          and last_value is not null
59          and last_value <= r.value then
60         rise := rise + (r.value - last_value);
61         run := run + ((extract(epoch from r.whence) +
62                        (extract(milliseconds from r.whence)::integer % 1000)/1000.0) -
63                       (extract(epoch from last_row_whence) +
64                        (extract(milliseconds from last_row_whence)::integer % 1000)/1000.0));
65       end if;
66     end if;
67   end if;
68   if r.value is not null then
69     last_row_whence := r.whence;
70     last_value := r.value;
71   end if;
72   end loop;
73   if rec.count_rows > 0 then
74     rec.avg_value := rec.avg_value / rec.count_rows;
75     if run is not null and run > 0 then
76       rec.counter_dev := rise/run;
77     end if;
78     return next rec;
79   end if;
80 return;
81 end;
82 $BODY$
83   LANGUAGE 'plpgsql' SECURITY DEFINER;
84  
85 GRANT EXECUTE ON FUNCTION stratcon.window_robust_derive(timestamp with time zone) TO stratcon;
86
Note: See TracBrowser for help on using the browser.