root/sql/sprocs/stratcon.unroll_metric_numeric.sql

Revision 31d5dd7ae5a1a551b1d6b995ac08588191019405, 3.6 kB (checked in by Depesz Lubaczewski <depesz@omniti.com>, 4 years ago)

oops, forgot to add previus v_sql

  • 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     SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;
20     IF NOT FOUND THEN
21         RAISE EXCEPTION 'Unknown rollup %', in_roll;
22     END IF;
23     v_begin   := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_start) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval;
24     v_adj_end := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_end) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval;
25     LOOP
26         EXIT WHEN v_begin > v_adj_end;
27
28         v_rollup_time := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval;
29         v_end         := LEAST( v_rollup_time + (v_conf.span * '1 second'::interval) - '1 second'::interval, v_adj_end );
30         v_min_i       := (ceil(extract('epoch' FROM v_begin ) / v_conf.seconds))::INT8 % (v_conf.span / v_conf.seconds);
31         v_max_i       := (extract('epoch' FROM v_end )::INT8 / v_conf.seconds) % (v_conf.span / v_conf.seconds);
32
33         -- field = coalesce( $x, field ) is a trick that can be said otherwise as:
34         -- ( $x is null or field = $x )
35         -- 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.
36         v_sql := 'SELECT * FROM metric_numeric_rollup_' || in_roll || ' WHERE ';
37         v_sql := v_sql || 'rollup_time = $1 AND sid = coalesce( $2, sid ) AND "name" = coalesce( $3, "name" )';
38
39         -- Following code (EXECUTE ... USING ... will work only from 8.4 on!
40         FOR temprec IN EXECUTE v_sql USING v_rollup_time, in_sid, in_name LOOP
41             v_row.sid  := temprec.sid;
42             v_row.name := temprec.name;
43             FOR v_i in v_min_i .. v_max_i LOOP
44                 v_row.rollup_time := v_rollup_time + '1 second'::INTERVAL * v_i * v_conf.seconds;
45                 v_row.count_rows  := temprec.count_rows[v_i + array_lower( temprec.count_rows, 1 ) ];
46                 v_row.avg_value   := NULL;
47                 v_row.counter_dev := NULL;
48                 IF v_row.count_rows > 0 THEN
49                     v_row.avg_value   := temprec.avg_value[v_i + array_lower( temprec.avg_value, 1 ) ];
50                     v_row.counter_dev := temprec.counter_dev[v_i + array_lower( temprec.counter_dev, 1 ) ];
51                 END IF;
52                 RETURN next v_row;
53             END LOOP;
54         END LOOP;
55         -- add a span
56         v_begin := v_begin + v_conf.span * '1 second'::interval;
57         -- trunc to the beginning of a span
58         v_begin := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval;
59     END LOOP;
60 END;
61 $$
62 LANGUAGE plpgsql
63 SECURITY DEFINER;
64
65 -- wrapper so we will NOT give (NULL, NULL,) when calling unroll for all sid AND names.
66 CREATE OR REPLACE FUNCTION stratcon.unroll_metric_numeric (timestamptz, timestamptz, text)
67 RETURNS SETOF stratcon.metric_numeric_rollup_segment
68 AS $$
69     SELECT * FROM stratcon.unroll_metric_numeric( NULL, NULL, $1, $2, $3 );
70 $$ language sql;
Note: See TracBrowser for help on using the browser.