root/sql/sprocs/stratcon.choose_window.sql

Revision 7303d8cb99b872bcc32bc3aad0f965352106c1d1, 2.3 kB (checked in by Robert Treat <robert@omniti.com>, 4 years ago)

not sure how valuable the meta info is, but no sense leaving it wrong

  • 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   v_window record;
8 begin
9   -- Figure out which table we should be looking in
10   for v_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                             'metric_numeric_rollup_5m' as atablename
22                   union all
23                      select 20*60 as isec, '20 minutes'::interval as aperiod,
24                             'metric_numeric_rollup_20m' as atablename
25                   union all
26                      select 30*60 as isec, '30 minutes'::interval as aperiod,
27                             'metric_numeric_rollup_30m' as atablename
28                   union all
29                      select 60*60 as isec, '1 hour'::interval as aperiod,
30                             'metric_numeric_rollup_1hour' as atablename
31                   union all
32                      select 4*60*60 as isec, '4 hours'::interval as aaperiod,
33                             'metric_numeric_rollup_4hour' as atablename
34                   union all
35                      select 24*60*60 as isec, '1 day'::interval as aperiod,
36                             'metric_numeric_rollup_1day' as atablename
37                  ) ivs
38          ) b
39  order by badness asc
40   limit 1
41   loop
42     tablename := v_window.atablename;
43     period := v_window.aperiod;
44     nperiods := v_window.anperiods;
45     return next;
46   end loop;
47   return;
48 end
49 $BODY$
50   LANGUAGE 'plpgsql' SECURITY DEFINER;
51
52 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.