root/sql/sprocs/stratcon.fetch_varset.sql

Revision 176662823c617f275f16583569d617151ef34731, 4.2 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

fetch varset functions

  • 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_end_adj timestamptz;
33   v_change_row noit.metric_text_changelog%rowtype;
34 begin
35   -- Map out uuid to an sid.
36   v_sid := in_sid;
37
38   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
39
40   select 'epoch'::timestamp +
41          ((floor(extract('epoch' from in_start_time) /
42                  extract('epoch' from v_target.period)) *
43            extract('epoch' from v_target.period)) || ' seconds') ::interval
44     into v_start_adj;
45
46   select 'epoch'::timestamp +
47          ((floor(extract('epoch' from in_end_time) /
48                  extract('epoch' from v_target.period)) *
49            extract('epoch' from v_target.period)) || ' seconds') ::interval
50     into v_end_adj;
51
52   for v_change_row in
53     select sid, 'epoch'::timestamp +
54          ((floor(extract('epoch' from whence) /
55                  extract('epoch' from v_target.period)) *
56            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
57            name, value
58       from noit.metric_text_changelog
59      where sid = v_sid
60        and name = in_name
61        and whence <= v_start_adj
62   order by 'epoch'::timestamp +
63          ((floor(extract('epoch' from whence) /
64                  extract('epoch' from v_target.period)) *
65            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
66      limit 1
67   loop
68     v_start_text := coalesce(v_change_row.value, '[unset]');
69   end loop;
70
71   for v_change_row in
72     select v_sid as sid, whence, in_name as name, value from
73 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
74 --      from generate_series(1, v_target.nperiods) t) s
75 -- left join
76     (select 'epoch'::timestamp +
77          ((floor(extract('epoch' from whence) /
78                  extract('epoch' from v_target.period)) *
79            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
80            coalesce(value, '[unset]') as value
81       from stratcon.loading_dock_metric_text_s_change_log
82      where sid = v_sid
83        and name = in_name
84        and whence > v_start_adj
85        and whence <= v_end_adj) d
86 --    using (whence)
87   order by whence asc
88   loop
89     v_next_text := v_change_row.value;
90     if v_change_row.value is not null and
91        v_start_text != v_change_row.value then
92       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
93     else
94       v_change_row.value := v_start_text;
95     end if;
96     if v_next_text is not null then
97       v_start_text := v_next_text;
98     end if;
99     return next v_change_row;
100   end loop;
101
102
103   if v_next_text is null then
104     -- No rows.
105     for v_change_row in
106       select v_sid as sid, v_start_adj as whence, in_name as name, v_start_text as value
107     loop
108       return next v_change_row;
109     end loop;
110   end if;
111
112   return;
113 end
114 $BODY$
115   LANGUAGE 'plpgsql' SECURITY DEFINER;;
116  
117 GRANT EXECUTE ON FUNCTION stratcon._fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer) TO stratcon;
118
Note: See TracBrowser for help on using the browser.