94 | | |
---|
95 | | -- Function To generate SID from ID |
---|
96 | | |
---|
97 | | CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) |
---|
98 | | RETURNS integer |
---|
99 | | AS $$ |
---|
100 | | DECLARE |
---|
101 | | v_ex_sid integer; |
---|
102 | | v_new_sid integer; |
---|
103 | | |
---|
104 | | BEGIN |
---|
105 | | |
---|
106 | | SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id |
---|
107 | | INTO v_ex_sid; |
---|
108 | | |
---|
109 | | IF NOT FOUND THEN |
---|
110 | | SELECT nextval('stratcon.seq_sid') |
---|
111 | | INTO v_new_sid; |
---|
112 | | |
---|
113 | | INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); |
---|
114 | | |
---|
115 | | RETURN v_new_sid; |
---|
116 | | ELSE |
---|
117 | | RETURN v_ex_sid; |
---|
118 | | END IF; |
---|
119 | | |
---|
120 | | END |
---|
121 | | $$ LANGUAGE plpgsql; |
---|
122 | | |
---|
123 | | -- Trigger Function to change Metrix Text Changes |
---|
124 | | |
---|
125 | | CREATE TRIGGER loading_dock_metric_text_s_change_log |
---|
126 | | AFTER INSERT ON loading_dock_metric_text_s |
---|
127 | | FOR EACH ROW |
---|
128 | | EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); |
---|
129 | | |
---|
130 | | CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger |
---|
131 | | AS $$ |
---|
132 | | DECLARE |
---|
133 | | v_oldvalue TEXT; |
---|
134 | | BEGIN |
---|
135 | | |
---|
136 | | IF TG_OP = 'INSERT' THEN |
---|
137 | | SELECT value FROM stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name |
---|
138 | | AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log |
---|
139 | | WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) |
---|
140 | | INTO v_oldvalue; |
---|
141 | | |
---|
142 | | IF v_oldvalue IS DISTINCT FROM NEW.value THEN |
---|
143 | | |
---|
144 | | INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) |
---|
145 | | VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); |
---|
146 | | |
---|
147 | | END IF; |
---|
148 | | |
---|
149 | | ELSE |
---|
150 | | RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; |
---|
151 | | END IF; |
---|
152 | | |
---|
153 | | RETURN NULL; |
---|
154 | | |
---|
155 | | END |
---|
156 | | $$ |
---|
157 | | LANGUAGE plpgsql; |
---|
158 | | |
---|
159 | | -- Trigger on Metrix Numeric to log last inserted timestamp |
---|
160 | | |
---|
161 | | CREATE TRIGGER loading_dock_metric_numeric_s_whence_log |
---|
162 | | AFTER INSERT ON loading_dock_metric_numeric_s |
---|
163 | | FOR EACH ROW |
---|
164 | | EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log(); |
---|
165 | | |
---|
166 | | |
---|
167 | | CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() |
---|
168 | | RETURNS trigger |
---|
169 | | AS $$ |
---|
170 | | DECLARE |
---|
171 | | v_whence timestamptz; |
---|
172 | | BEGIN |
---|
173 | | IF TG_OP = 'INSERT' THEN |
---|
174 | | 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 |
---|
175 | | INTO v_whence; |
---|
176 | | IF NOT FOUND THEN |
---|
177 | | INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval); |
---|
178 | | END IF; |
---|
179 | | END IF; |
---|
180 | | |
---|
181 | | RETURN NULL; |
---|
182 | | END |
---|
183 | | $$ |
---|
184 | | LANGUAGE plpgsql; |
---|
185 | | |
---|
186 | | |
---|
187 | | |
---|
188 | | -- Generic rollup function (under progress) |
---|
189 | | |
---|
190 | | |
---|
191 | | |
---|
192 | | CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric() |
---|
193 | | RETURNS void |
---|
194 | | AS $$ |
---|
195 | | |
---|
196 | | DECLARE |
---|
197 | | |
---|
198 | | v_min_whence TIMESTAMPTZ; |
---|
199 | | v_max_rollup_5 TIMESTAMPTZ; |
---|
200 | | v_cur_time TIMESTAMPTZ; |
---|
201 | | |
---|
202 | | BEGIN |
---|
203 | | |
---|
204 | | select min(whence) from stratcon.log_whence_s |
---|
205 | | INTO v_min_whence; |
---|
206 | | |
---|
207 | | select max(rollup_time) from stratcon.rollup_matrix_numeric_5m |
---|
208 | | INTO v_max_rollup_5; |
---|
209 | | select now() |
---|
210 | | INTO v_cur_time; |
---|
211 | | |
---|
212 | | IF v_max_rollup_5 IS NULL THEN |
---|
213 | | v_max_rollup_5:=timestamp '2008-01-01 00:00:00'; |
---|
214 | | END IF; |
---|
215 | | |
---|
216 | | |
---|
217 | | IF v_min_whence >= v_max_rollup_5 THEN |
---|
218 | | |
---|
219 | | -- 5 MINUTES ROLLUP |
---|
220 | | |
---|
221 | | PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence); |
---|
222 | | |
---|
223 | | -- HOURLY ROLLUP |
---|
224 | | |
---|
225 | | IF extract('minutes' from v_cur_time)>55 and extract('minutes' from v_cur_time)<59 THEN |
---|
226 | | |
---|
227 | | PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); |
---|
228 | | |
---|
229 | | END IF; |
---|
230 | | |
---|
231 | | -- DELETE FROM LOG TABLE |
---|
232 | | |
---|
233 | | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; |
---|
234 | | |
---|
235 | | ELSIF v_min_whence < v_max_rollup_5 THEN |
---|
236 | | |
---|
237 | | -- 5 MINUTES ROLLUP |
---|
238 | | |
---|
239 | | DELETE FROM stratcon.rollup_matrix_numeric_5m |
---|
240 | | WHERE rollup_time = date_trunc('minutes',v_min_whence); |
---|
241 | | |
---|
242 | | PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence ,v_max_rollup_5); |
---|
243 | | |
---|
244 | | -- HOURLY ROLLUP |
---|
245 | | |
---|
246 | | DELETE FROM stratcon.rollup_matrix_numeric_60m |
---|
247 | | WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence); |
---|
248 | | |
---|
249 | | PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); |
---|
250 | | |
---|
251 | | -- DELETE FROM LOG TABLE |
---|
252 | | |
---|
253 | | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; |
---|
254 | | |
---|
255 | | ELSE |
---|
256 | | |
---|
257 | | RETURN; |
---|
258 | | |
---|
259 | | END IF; |
---|
260 | | |
---|
261 | | RETURN; |
---|
262 | | |
---|
263 | | EXCEPTION |
---|
264 | | WHEN RAISE_EXCEPTION THEN |
---|
265 | | RAISE EXCEPTION '%', SQLERRM; |
---|
266 | | WHEN OTHERS THEN |
---|
267 | | RAISE NOTICE '%', SQLERRM; |
---|
268 | | END |
---|
269 | | $$ LANGUAGE plpgsql; |
---|
270 | | |
---|
271 | | --- 5 minutes rollup |
---|
272 | | |
---|
273 | | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz) |
---|
274 | | RETURNS void |
---|
275 | | AS $$ |
---|
276 | | DECLARE |
---|
277 | | |
---|
278 | | rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
279 | | v_sql TEXT; |
---|
280 | | |
---|
281 | | BEGIN |
---|
282 | | |
---|
283 | | FOR rec IN |
---|
284 | | SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, |
---|
285 | | COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value |
---|
286 | | FROM stratcon.loading_dock_metric_numeric_s |
---|
287 | | WHERE WHENCE < date_trunc('minutes',v_min_whence) AND WHENCE >= date_trunc('minutes',v_min_whence)-'5 minutes'::interval |
---|
288 | | GROUP BY rollup_time,sid,name |
---|
289 | | |
---|
290 | | LOOP |
---|
291 | | |
---|
292 | | INSERT INTO stratcon.rollup_matrix_numeric_5m |
---|
293 | | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
294 | | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
295 | | |
---|
296 | | |
---|
297 | | END LOOP; |
---|
298 | | |
---|
299 | | |
---|
300 | | RETURN; |
---|
301 | | |
---|
302 | | EXCEPTION |
---|
303 | | WHEN RAISE_EXCEPTION THEN |
---|
304 | | RAISE EXCEPTION '%', SQLERRM; |
---|
305 | | WHEN OTHERS THEN |
---|
306 | | RAISE NOTICE '%', SQLERRM; |
---|
307 | | END |
---|
308 | | $$ LANGUAGE plpgsql; |
---|
309 | | |
---|
310 | | --- Hourly rollup |
---|
311 | | |
---|
312 | | |
---|
313 | | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz) |
---|
314 | | RETURNS void |
---|
315 | | AS $$ |
---|
316 | | DECLARE |
---|
317 | | rec stratcon.rollup_matrix_numeric_60m%rowtype; |
---|
318 | | v_sql TEXT; |
---|
319 | | |
---|
320 | | BEGIN |
---|
321 | | FOR rec IN |
---|
322 | | SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, |
---|
323 | | SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, |
---|
324 | | MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
325 | | FROM stratcon.rollup_matrix_numeric_5m |
---|
326 | | WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) |
---|
327 | | GROUP BY date_trunc('hour',rollup_time),sid,name |
---|
328 | | LOOP |
---|
329 | | |
---|
330 | | INSERT INTO stratcon.rollup_matrix_numeric_60m |
---|
331 | | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
332 | | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
333 | | |
---|
334 | | END LOOP; |
---|
335 | | RETURN; |
---|
336 | | |
---|
337 | | EXCEPTION |
---|
338 | | WHEN RAISE_EXCEPTION THEN |
---|
339 | | RAISE EXCEPTION '%', SQLERRM; |
---|
340 | | WHEN OTHERS THEN |
---|
341 | | RAISE NOTICE '%', SQLERRM; |
---|
342 | | END |
---|
343 | | $$ LANGUAGE plpgsql; |
---|
| 113 | |
---|
| 114 | |
---|
| 115 | -- Function To generate SID from ID |
---|
| 116 | |
---|
| 117 | CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) |
---|
| 118 | RETURNS integer |
---|
| 119 | AS $$ |
---|
| 120 | DECLARE |
---|
| 121 | v_ex_sid integer; |
---|
| 122 | v_new_sid integer; |
---|
| 123 | |
---|
| 124 | BEGIN |
---|
| 125 | |
---|
| 126 | SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id |
---|
| 127 | INTO v_ex_sid; |
---|
| 128 | |
---|
| 129 | IF NOT FOUND THEN |
---|
| 130 | SELECT nextval('stratcon.seq_sid') |
---|
| 131 | INTO v_new_sid; |
---|
| 132 | |
---|
| 133 | INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); |
---|
| 134 | |
---|
| 135 | RETURN v_new_sid; |
---|
| 136 | ELSE |
---|
| 137 | RETURN v_ex_sid; |
---|
| 138 | END IF; |
---|
| 139 | |
---|
| 140 | END |
---|
| 141 | $$ LANGUAGE plpgsql; |
---|
| 142 | |
---|
| 143 | -- Trigger Function to change Metrix Text Changes |
---|
| 144 | |
---|
| 145 | CREATE TRIGGER loading_dock_metric_text_s_change_log |
---|
| 146 | AFTER INSERT ON loading_dock_metric_text_s |
---|
| 147 | FOR EACH ROW |
---|
| 148 | EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); |
---|
| 149 | |
---|
| 150 | CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger |
---|
| 151 | AS $$ |
---|
| 152 | DECLARE |
---|
| 153 | v_oldvalue TEXT; |
---|
| 154 | BEGIN |
---|
| 155 | |
---|
| 156 | IF TG_OP = 'INSERT' THEN |
---|
| 157 | SELECT value FROM stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name |
---|
| 158 | AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log |
---|
| 159 | WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) |
---|
| 160 | INTO v_oldvalue; |
---|
| 161 | |
---|
| 162 | IF v_oldvalue IS DISTINCT FROM NEW.value THEN |
---|
| 163 | |
---|
| 164 | INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) |
---|
| 165 | VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); |
---|
| 166 | |
---|
| 167 | END IF; |
---|
| 168 | |
---|
| 169 | ELSE |
---|
| 170 | RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; |
---|
| 171 | END IF; |
---|
| 172 | |
---|
| 173 | RETURN NULL; |
---|
| 174 | |
---|
| 175 | END |
---|
| 176 | $$ |
---|
| 177 | LANGUAGE plpgsql; |
---|
| 178 | |
---|
| 179 | -- Trigger on Metrix Numeric to log last inserted timestamp |
---|
| 180 | |
---|
| 181 | CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() |
---|
| 182 | RETURNS trigger |
---|
| 183 | AS $$ |
---|
| 184 | DECLARE |
---|
| 185 | v_whence timestamptz; |
---|
| 186 | BEGIN |
---|
| 187 | IF TG_OP = 'INSERT' THEN |
---|
| 188 | 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 and interval='5 minutes' |
---|
| 189 | INTO v_whence; |
---|
| 190 | IF NOT FOUND THEN |
---|
| 191 | INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes'); |
---|
| 192 | END IF; |
---|
| 193 | END IF; |
---|
| 194 | |
---|
| 195 | RETURN NULL; |
---|
| 196 | END |
---|
| 197 | $$ |
---|
| 198 | LANGUAGE plpgsql; |
---|
| 199 | |
---|
| 200 | -- 5 minutes rollup |
---|
| 201 | |
---|
| 202 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() |
---|
| 203 | RETURNS void |
---|
| 204 | AS $$ |
---|
| 205 | DECLARE |
---|
| 206 | |
---|
| 207 | rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
| 208 | v_sql TEXT; |
---|
| 209 | v_min_whence TIMESTAMPTZ; |
---|
| 210 | v_max_rollup_5 TIMESTAMPTZ; |
---|
| 211 | v_whence TIMESTAMPTZ; |
---|
| 212 | |
---|
| 213 | BEGIN |
---|
| 214 | |
---|
| 215 | SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' |
---|
| 216 | INTO v_min_whence; |
---|
| 217 | |
---|
| 218 | SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m |
---|
| 219 | INTO v_max_rollup_5; |
---|
| 220 | |
---|
| 221 | IF v_min_whence < v_max_rollup_5 THEN |
---|
| 222 | |
---|
| 223 | DELETE FROM stratcon.rollup_matrix_numeric_5m |
---|
| 224 | WHERE rollup_time = v_min_whence; |
---|
| 225 | |
---|
| 226 | ELSIF v_min_whence = v_max_rollup_5 THEN |
---|
| 227 | |
---|
| 228 | DELETE FROM stratcon.log_whence_s |
---|
| 229 | WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; |
---|
| 230 | |
---|
| 231 | RETURN; |
---|
| 232 | |
---|
| 233 | END IF; |
---|
| 234 | |
---|
| 235 | FOR rec IN |
---|
| 236 | SELECT sid , name,v_min_whence as rollup_time, |
---|
| 237 | COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value |
---|
| 238 | FROM stratcon.loading_dock_metric_numeric_s |
---|
| 239 | WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval |
---|
| 240 | GROUP BY rollup_time,sid,name |
---|
| 241 | |
---|
| 242 | LOOP |
---|
| 243 | |
---|
| 244 | INSERT INTO stratcon.rollup_matrix_numeric_5m |
---|
| 245 | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 246 | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 247 | |
---|
| 248 | END LOOP; |
---|
| 249 | |
---|
| 250 | -- Insert Log for Hourly rollup |
---|
| 251 | |
---|
| 252 | SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' |
---|
| 253 | INTO v_whence; |
---|
| 254 | IF NOT FOUND THEN |
---|
| 255 | INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); |
---|
| 256 | END IF; |
---|
| 257 | |
---|
| 258 | |
---|
| 259 | -- Delete from whence log table |
---|
| 260 | |
---|
| 261 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; |
---|
| 262 | |
---|
| 263 | RETURN; |
---|
| 264 | |
---|
| 265 | EXCEPTION |
---|
| 266 | WHEN RAISE_EXCEPTION THEN |
---|
| 267 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 268 | WHEN OTHERS THEN |
---|
| 269 | RAISE NOTICE '%', SQLERRM; |
---|
| 270 | END |
---|
| 271 | $$ LANGUAGE plpgsql; |
---|
| 272 | |
---|
| 273 | -- 1 hourl rollup |
---|
| 274 | |
---|
| 275 | |
---|
| 276 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() |
---|
| 277 | RETURNS void |
---|
| 278 | AS $$ |
---|
| 279 | DECLARE |
---|
| 280 | rec stratcon.rollup_matrix_numeric_60m%rowtype; |
---|
| 281 | v_sql TEXT; |
---|
| 282 | v_min_whence TIMESTAMPTZ; |
---|
| 283 | v_max_rollup_5 TIMESTAMPTZ; |
---|
| 284 | |
---|
| 285 | BEGIN |
---|
| 286 | |
---|
| 287 | SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' |
---|
| 288 | INTO v_min_whence; |
---|
| 289 | |
---|
| 290 | SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_60m |
---|
| 291 | INTO v_max_rollup_5; |
---|
| 292 | |
---|
| 293 | IF v_min_whence <= v_max_rollup_5 THEN |
---|
| 294 | |
---|
| 295 | DELETE FROM stratcon.rollup_matrix_numeric_60m |
---|
| 296 | WHERE rollup_time= v_min_whence; |
---|
| 297 | |
---|
| 298 | END IF; |
---|
| 299 | |
---|
| 300 | FOR rec IN |
---|
| 301 | SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, |
---|
| 302 | SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, |
---|
| 303 | MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
| 304 | FROM stratcon.rollup_matrix_numeric_5m |
---|
| 305 | WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) |
---|
| 306 | GROUP BY date_trunc('hour',rollup_time),sid,name |
---|
| 307 | LOOP |
---|
| 308 | |
---|
| 309 | INSERT INTO stratcon.rollup_matrix_numeric_60m |
---|
| 310 | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 311 | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 312 | |
---|
| 313 | END LOOP; |
---|
| 314 | |
---|
| 315 | |
---|
| 316 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; |
---|
| 317 | |
---|
| 318 | RETURN; |
---|
| 319 | |
---|
| 320 | EXCEPTION |
---|
| 321 | WHEN RAISE_EXCEPTION THEN |
---|
| 322 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 323 | WHEN OTHERS THEN |
---|
| 324 | RAISE NOTICE '%', SQLERRM; |
---|
| 325 | END |
---|
| 326 | $$ LANGUAGE plpgsql; |
---|
| 327 | |
---|
| 328 | |
---|