Changeset 3a1a63f5dd675d5b58def4ed6e2727db597f31b5 for sql
 Timestamp:
 03/27/08 20:51:08 (8 years ago)
 gitparent:
 Files:

 sql/schema.sql (modified) (7 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/schema.sql
r0dbb5eb r3a1a63f 165 165 166 166 167 CREATE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() RETURNS trigger 168 AS $$ 167 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 168 RETURNS trigger 169 AS $$ 169 170 DECLARE 170 171 v_whence timestamptz; 171 172 BEGIN 172 173 IF TG_OP = 'INSERT' THEN 173 SELECT whence FROM stratcon.log_whence_s WHERE whence= NEW.whence174 SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval 174 175 INTO v_whence; 175 176 IF NOT FOUND THEN 176 UPDATE stratcon.log_whence_s SET whence=NEW.whence;177 INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval); 177 178 END IF; 178 ELSE179 RAISE EXCEPTION 'NonINSERT DML operation attempted on INSERT only table';180 179 END IF; 181 180 … … 187 186 188 187 189 190 188  Generic rollup function (under progress) 191 189 … … 200 198 v_min_whence TIMESTAMPTZ; 201 199 v_max_rollup_5 TIMESTAMPTZ; 202 v_ max_rollup_60TIMESTAMPTZ;200 v_cur_time TIMESTAMPTZ; 203 201 204 202 BEGIN … … 209 207 select max(rollup_time) from stratcon.rollup_matrix_numeric_5m 210 208 INTO v_max_rollup_5; 211 212 select max(rollup_time) from stratcon.rollup_matrix_numeric_60m 213 INTO v_max_rollup_60; 209 select now() 210 INTO v_cur_time; 214 211 215 212 IF v_max_rollup_5 IS NULL THEN … … 217 214 END IF; 218 215 219 IF v_max_rollup_60 IS NULL THEN220 v_max_rollup_60:=timestamp '20080101 00:00:00';221 END IF;222 223 216 224 217 IF v_min_whence >= v_max_rollup_5 THEN … … 230 223  HOURLY ROLLUP 231 224 232 IF date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60)THEN225 IF extract('minutes' from v_cur_time)>55 and extract('minutes' from v_cur_time)<59 THEN 233 226 234 227 PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); … … 369 362 370 363 COMMIT; 364