Show
Ignore:
Timestamp:
10/23/09 14:56:37 (4 years ago)
Author:
Theo Schlossnagle <jesus@omniti.com>
git-committer:
Theo Schlossnagle <jesus@omniti.com> 1256309797 +0000
git-parent:

[f0c2368bc65ecc30e12507bd8c0ce078fd060a4a]

git-author:
Theo Schlossnagle <jesus@omniti.com> 1256309797 +0000
Message:

rework the rollups to be generic

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/sprocs/stratcon.fetch_dataset.sql

    rf805e6b ra22c5e5  
    4242    v_start_adj     timestamptz; 
    4343    v_end_adj       timestamptz; 
     44    v_rollup        text; 
    4445    v_l_rollup_row  stratcon.metric_numeric_rollup_segment%rowtype; 
    4546    v_rollup_row    stratcon.metric_numeric_rollup_segment%rowtype; 
    …… 
    5354    END IF; 
    5455 
     56    select array_to_string(regexp_matches (v_target.tablename, '_([^_]+)$'),'') into v_rollup; 
     57 
    5558    -- round start and end timestamps to period precision (i.e. to 5 minutes, or 1 hour, or ...) 
    5659    v_start_adj := ( 'epoch'::timestamp + v_target.period * floor( extract('epoch' from in_start_time) / v_target.epoch_period ) ) AT TIME ZONE 'UTC'; 
    …… 
    5962    -- build sql using placeholders ([something]) to make it more readable than using ' || ... || ' all the time. 
    6063    v_sql_subtable := $SQL$ 
    61         select * 
    62         from stratcon.[tablename] 
    63         where 
    64             sid = [in_sid] 
    65             and "name" = [in_name] 
    66             and rollup_time between [v_start_adj]::timestamp AND [v_end_adj]::timestamp 
     64        SELECT * 
     65        FROM stratcon.unroll_metric_numeric( 
     66            [in_sid], 
     67            [in_name], 
     68            [v_start_adj]::timestamp, 
     69            [v_end_adj]::timestamp, 
     70            [v_rollup] 
     71        ) 
    6772    $SQL$; 
    68     IF v_target.tablename = 'metric_numeric_rollup_5m' THEN 
    69  
    70         v_sql_subtable := $SQL$ 
    71             SELECT * 
    72             FROM stratcon.unroll_metric_numeric_5m( 
    73                 [in_sid], 
    74                 [in_name], 
    75                 [v_start_adj]::timestamp, 
    76                 [v_end_adj]::timestamp 
    77             ) 
    78         $SQL$; 
    79  
    80     END IF; 
    8173 
    8274    v_sql := $SQL$ 
    …… 
    10496    v_sql := replace( v_sql, '[nperiods]',    v_target.nperiods::TEXT           ); 
    10597    v_sql := replace( v_sql, '[tablename]',   quote_ident( v_target.tablename ) ); 
     98    v_sql := replace( v_sql, '[v_rollup]',    quote_literal( v_rollup )         ); 
    10699 
    107100    FOR v_rollup_row IN EXECUTE v_sql LOOP