root/sql/sprocs/stratcon.fetch_varset.sql

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

fix varset fetching

  • Property mode set to 100644
Line 
1 -- Function: stratcon.fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer)
2
3 CREATE OR REPLACE FUNCTION stratcon.fetch_varset(in_check uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer)
4   RETURNS SETOF noit.metric_text_changelog AS
5 $BODY$
6 declare
7   v_sid int;
8 begin
9   -- Map out uuid to an sid.
10   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
11   if not found then
12     return;
13   end if;
14
15   return query select * from stratcon.fetch_varset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods);
16 end
17 $BODY$
18   LANGUAGE 'plpgsql'  SECURITY DEFINER;
19  
20 GRANT EXECUTE ON FUNCTION stratcon.fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer) TO stratcon;
21
22 -- Function: stratcon.fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer)
23
24 CREATE OR REPLACE FUNCTION stratcon.fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer)
25   RETURNS SETOF noit.metric_text_changelog AS
26 $BODY$declare
27   v_sid int;
28   v_target record;
29   v_start_adj timestamptz;
30   v_start_text text;
31   v_next_text text;
32   v_prev_text boolean;
33   v_end_adj timestamptz;
34   v_change_row noit.metric_text_changelog%rowtype;
35 begin
36   -- Map out uuid to an sid.
37   v_prev_text := false;
38   v_sid := in_sid;
39
40   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
41
42   select 'epoch'::timestamp +
43          ((floor(extract('epoch' from in_start_time) /
44                  extract('epoch' from v_target.period)) *
45            extract('epoch' from v_target.period)) || ' seconds') ::interval
46     into v_start_adj;
47
48   select 'epoch'::timestamp +
49          ((floor(extract('epoch' from in_end_time) /
50                  extract('epoch' from v_target.period)) *
51            extract('epoch' from v_target.period)) || ' seconds') ::interval
52     into v_end_adj;
53
54   for v_change_row in
55     select sid, 'epoch'::timestamp +
56          ((floor(extract('epoch' from whence) /
57                  extract('epoch' from v_target.period)) *
58            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
59            name, value
60       from metric_text_changelog
61      where sid = v_sid
62        and name = in_name
63        and whence <= v_start_adj
64   order by 'epoch'::timestamp +
65          ((floor(extract('epoch' from whence) /
66                  extract('epoch' from v_target.period)) *
67            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
68      limit 1
69   loop
70     v_start_text := coalesce(v_change_row.value, '[unset]');
71   end loop;
72
73   for v_change_row in
74     select v_sid as sid, whence, in_name as name, value from
75 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
76 --      from generate_series(1, v_target.nperiods) t) s
77 -- left join
78     (select 'epoch'::timestamp +
79          ((floor(extract('epoch' from whence) /
80                  extract('epoch' from v_target.period)) *
81            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
82            coalesce(value, '[unset]') as value
83       from metric_text_changelog
84      where sid = v_sid
85        and name = in_name
86        and whence > v_start_adj
87        and whence <= v_end_adj) d
88 --    using (whence)
89   order by whence asc
90   loop
91
92     v_next_text := v_change_row.value;
93     if v_change_row.value is not null and not v_prev_text and
94        (v_start_text is null or v_start_text <> v_change_row.value) then
95       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
96     else
97       v_change_row.value := coalesce(v_change_row.value, '[unset]');
98     end if;
99     if v_start_text is null and v_next_text is not null then
100       v_start_text := v_next_text;
101     end if;
102     v_prev_text := true;
103     return next v_change_row;
104   end loop;
105
106
107   if v_next_text is null then
108     -- No rows.
109     for v_change_row in
110       select v_sid as sid, v_start_adj as whence, in_name as name, v_start_text as value
111     loop
112       return next v_change_row;
113     end loop;
114   end if;
115
116   return;
117 end
118 $BODY$
119   LANGUAGE 'plpgsql' SECURITY DEFINER;
120  
121 GRANT EXECUTE ON FUNCTION stratcon.fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer) TO stratcon;
122
Note: See TracBrowser for help on using the browser.