root/sql/sprocs/stratcon.choose_window.sql

Revision 161a5c5dbc37986a5d1c5ce66869d4504f97f0eb, 2.2 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

Change schema to stratcon and modified functions to be secturity definer

  • Property mode set to 100644
Line 
1 -- Function: stratcon.choose_window(timestamp with time zone, timestamp with time zone, integer)
2
3 CREATE OR REPLACE FUNCTION stratcon.choose_window(IN in_start_time timestamp with time zone, IN in_end_time timestamp with time zone, IN in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer)
4   RETURNS SETOF record AS
5 $BODY$
6 declare
7   window record;
8 begin
9   -- Figure out which table we should be looking in
10   for window in
11     select atablename, aperiod, anperiods
12     from (select aperiod, round(iv/isec) ::integer as anperiods, atablename,
13                  abs(case when iv/isec - in_hopeful_nperiods < 0
14                           then 10 * (in_hopeful_nperiods - iv/isec)
15                           else iv/isec - in_hopeful_nperiods
16                            end) as badness
17             from (select extract('epoch' from in_end_time) -
18                          extract('epoch' from in_start_time) as iv
19                  ) i,
20                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
21                             'noit.metric_numeric_rollup_5m' as atablename
22                   union all
23                      select 20*60 as isec, '20 minutes'::interval as aperiod,
24                             'noit.metric_numeric_rollup_20m' as atablename
25                   union all
26                      select 60*60 as isec, '1 hour'::interval as aperiod,
27                             'noit.metric_numeric_rollup_60m' as atablename
28                   union all
29                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
30                             'noit.metric_numeric_rollup_6hours' as atablename
31                   union all
32                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
33                             'noit.metric_numeric_rollup_12hours' as atablename
34                  ) ivs
35          ) b
36  order by badness asc
37   limit 1
38   loop
39     tablename := window.atablename;
40     period := window.aperiod;
41     nperiods := window.anperiods;
42     return next;
43   end loop;
44   return;
45 end
46 $BODY$
47   LANGUAGE 'plpgsql' SECURITY DEFINER;
48
49 GRANT EXECUTE ON FUNCTION stratcon.choose_window(timestamp with time zone, timestamp with time zone, integer) TO stratcon;
Note: See TracBrowser for help on using the browser.