root/sql/sprocs/stratcon.unroll_metric_numeric.sql

Revision b354e99dbd9c5a7fed905a1de2014ea0ed2dc507, 3.7 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 5 years ago)

sweep

  • Property mode set to 100644
Line 
1
2 CREATE OR REPLACE FUNCTION stratcon.unroll_metric_numeric
3 (in_sid integer, in_name text, in_start timestamp with time zone, in_end timestamp with time zone, in_roll text)
4 RETURNS SETOF stratcon.metric_numeric_rollup_segment
5 AS $$
6 DECLARE
7     v_row           stratcon.metric_numeric_rollup_segment%rowtype;
8     v_begin         TIMESTAMPTZ;
9     v_end           TIMESTAMPTZ;
10     v_adj_end       TIMESTAMPTZ;
11     v_min_i         INT4;
12     v_max_i         INT4;
13     v_rollup_time   TIMESTAMPTZ;
14     v_conf          RECORD;
15     v_i             INT4;
16     v_sql           TEXT;
17     temprec         RECORD;
18 BEGIN
19     IF in_start IS NULL OR in_end IS NULL THEN
20       RAISE EXCEPTION 'start and end must be not null (%,%)', in_start, in_end;
21     END IF;
22     SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;
23     IF NOT FOUND THEN
24         RAISE EXCEPTION 'Unknown rollup %', in_roll;
25     END IF;
26     v_begin   := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_start) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval;
27     v_adj_end := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_end) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval;
28     LOOP
29         EXIT WHEN v_begin > v_adj_end;
30
31         v_rollup_time := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval;
32         v_end         := LEAST( v_rollup_time + (v_conf.span * '1 second'::interval) - '1 second'::interval, v_adj_end );
33         v_min_i       := (ceil(extract('epoch' FROM v_begin ) / v_conf.seconds))::INT8 % (v_conf.span / v_conf.seconds);
34         v_max_i       := (extract('epoch' FROM v_end )::INT8 / v_conf.seconds) % (v_conf.span / v_conf.seconds);
35
36         -- field = coalesce( $x, field ) is a trick that can be said otherwise as:
37         -- ( $x is null or field = $x )
38         -- which means - for every given sid/name - we will search for only this sid/name. But when given NULLs, it will return data on all sid/names.
39         v_sql := 'SELECT * FROM metric_numeric_rollup_' || in_roll || ' WHERE ';
40         v_sql := v_sql || 'rollup_time = $1 AND sid = coalesce( $2, sid ) AND "name" = coalesce( $3, "name" )';
41
42         -- Following code (EXECUTE ... USING ... will work only from 8.4 on!
43         FOR temprec IN EXECUTE v_sql USING v_rollup_time, in_sid, in_name LOOP
44             v_row.sid  := temprec.sid;
45             v_row.name := temprec.name;
46             FOR v_i in v_min_i .. v_max_i LOOP
47                 v_row.rollup_time := v_rollup_time + '1 second'::INTERVAL * v_i * v_conf.seconds;
48                 v_row.count_rows  := temprec.count_rows[v_i + array_lower( temprec.count_rows, 1 ) ];
49                 v_row.avg_value   := NULL;
50                 v_row.counter_dev := NULL;
51                 IF v_row.count_rows > 0 THEN
52                     v_row.avg_value   := temprec.avg_value[v_i + array_lower( temprec.avg_value, 1 ) ];
53                     v_row.counter_dev := temprec.counter_dev[v_i + array_lower( temprec.counter_dev, 1 ) ];
54                 END IF;
55                 RETURN next v_row;
56             END LOOP;
57         END LOOP;
58         -- add a span
59         v_begin := v_begin + v_conf.span * '1 second'::interval;
60         -- trunc to the beginning of a span
61         v_begin := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval;
62     END LOOP;
63 END;
64 $$
65 LANGUAGE plpgsql
66 SECURITY DEFINER;
67
68 -- wrapper so we will NOT give (NULL, NULL,) when calling unroll for all sid AND names.
69 CREATE OR REPLACE FUNCTION stratcon.unroll_metric_numeric (timestamptz, timestamptz, text)
70 RETURNS SETOF stratcon.metric_numeric_rollup_segment
71 AS $$
72     SELECT * FROM stratcon.unroll_metric_numeric( NULL, NULL, $1, $2, $3 );
73 $$ language sql;
Note: See TracBrowser for help on using the browser.