Changeset 848c999201996fa821ee897f8bc9e3c0979a575f
 Timestamp:
 10/07/08 14:29:57
(10 years ago)
 Author:
 Denish Patel <denish@omniti.com>
 gitcommitter:
 Denish Patel <denish@omniti.com> 1223389797 +0000
 gitparent:
[1a8ddc4c1234e664bfd96ff9a8ef95d4b07079f2]
 gitauthor:
 Denish Patel <denish@omniti.com> 1223389797 +0000
 Message:
pg ddl export

Files:

Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
rfb7be7b 
r848c999 

429  429  rollup_time timestamp with time zone NOT NULL, 

430  430  count_rows integer, 

431   avg_value numeric 

 431  avg_value numeric, 

 432  counter_dev numeric 

432  433  ); 

433  434  

…  …  
444  445  rollup_time timestamp with time zone NOT NULL, 

445  446  count_rows integer, 

446   avg_value numeric 

 447  avg_value numeric, 

 448  counter_dev numeric 

447  449  ); 

448  450  

…  …  
459  461  rollup_time timestamp with time zone NOT NULL, 

460  462  count_rows integer, 

461   avg_value numeric 

 463  avg_value numeric, 

 464  counter_dev numeric 

462  465  ); 

463  466  

…  …  
474  477  rollup_time timestamp with time zone NOT NULL, 

475  478  count_rows integer, 

476   avg_value numeric 

 479  avg_value numeric, 

 480  counter_dev numeric 

477  481  ); 

478  482  

…  …  
489  493  rollup_time timestamp with time zone NOT NULL, 

490  494  count_rows integer, 

491   avg_value numeric 

 495  avg_value numeric, 

 496  counter_dev numeric 

492  497  ); 

493  498  

…  …  
705  710  

706  711  CREATE FUNCTION trig_update_tsvector_saved_graphs() RETURNS trigger 

707   AS $$ 

708   DECLARE 

 712  AS $$DECLARE 

709  713  BEGIN 

710  714  IF TG_OP != 'INSERT' THEN 

711   IF (NEW.graph_tags <> OLD.graph_tags) THEN 

 715  IF (NEW.graph_tags <> OLD.graph_tags OR NEW.title <> OLD.title) THEN 

712  716  UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid) where graphid=NEW.graphid; 

713  717  END IF; 

…  …  
1474  1478  

1475  1479  CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void 

1476   AS $$ 

1477   DECLARE 

 1480  AS $$DECLARE 

1478  1481  rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

1479  1482  v_sql TEXT; 

…  …  
1534  1537  

1535  1538  FOR rec IN 

1536   SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 

 1539  SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

 1540  (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 

1537  1541  FROM stratcon.rollup_matrix_numeric_6hours 

1538  1542  WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'12 hour'::interval 

…  …  
1542  1546  

1543  1547  INSERT INTO stratcon.rollup_matrix_numeric_12hours 

1544   (sid,name,rollup_time,count_rows,avg_value) VALUES 

1545   (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 

 1548  (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 

 1549  (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 

1546  1550  

1547  1551  END LOOP; 

…  …  
1576  1580  

1577  1581  CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void 

1578   AS $$ 

1579   DECLARE 

 1582  AS $$DECLARE 

1580  1583  

1581  1584  rec stratcon.rollup_matrix_numeric_20m%rowtype; 

…  …  
1637  1640  FOR rec IN 

1638  1641  SELECT sid , name,v_min_whence as rollup_time, 

1639   SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 

 1642  SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

 1643  (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 

1640  1644  FROM stratcon.rollup_matrix_numeric_5m 

1641  1645  WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence '20 minutes'::interval 

…  …  
1646  1650  

1647  1651  INSERT INTO stratcon.rollup_matrix_numeric_20m 

1648   (sid,name,rollup_time,count_rows,avg_value) VALUES 

1649   (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 

 1652  (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 

 1653  (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 

1650  1654  

1651  1655  END LOOP; 

…  …  
1682  1686  

1683  1687  CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void 

1684   AS $$ 

1685   DECLARE 

 1688  AS $$DECLARE 

1686  1689  

1687  1690  rec stratcon.rollup_matrix_numeric_5m%rowtype; 

…  …  
1741  1744  END IF; 

1742  1745  

1743   FOR rec IN 

1744   SELECT sid , name,v_min_whence as rollup_time, 

1745   COUNT(1) as count_rows ,AVG(value) as avg_value 

1746   FROM stratcon.loading_dock_metric_numeric_s 

1747   WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence '5 minutes'::interval 

1748   GROUP BY rollup_time,sid,name 

 1746  FOR rec IN 

 1747  

 1748  select n.sid, n.name, n.rollup_time, n.count_rows, n.avg_value, 

 1749  case when n.avg_value  l.avg_value >= 0 

 1750  then (n.avg_value  l.avg_value)/300.0 

 1751  else null end as counter_dev 

 1752  from (SELECT sid, name, v_min_whence as rollup_time, 

 1753  COUNT(1) as count_rows, avg(value) as avg_value 

 1754  FROM stratcon.loading_dock_metric_numeric_s 

 1755  WHERE whence <= v_min_whence AND whence > v_min_whence '5 minutes'::interval 

 1756  GROUP BY rollup_time,sid,name) as n 

 1757  left join stratcon.rollup_matrix_numeric_5m as l 

 1758  on (n.sid=l.sid and n.name=l.name and 

 1759  n.rollup_time  '5 minute'::interval = l.rollup_time) 

1749  1760  

1750  1761  LOOP 

…  …  
1752  1763  

1753  1764  INSERT INTO stratcon.rollup_matrix_numeric_5m 

1754   (sid,name,rollup_time,count_rows,avg_value) VALUES 

1755   (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 

 1765  (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 

 1766  (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 

1756  1767  

1757  1768  END LOOP; 

…  …  
1788  1799  

1789  1800  CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void 

1790   AS $$ 

1791   DECLARE 

 1801  AS $$DECLARE 

1792  1802  rec stratcon.rollup_matrix_numeric_60m%rowtype; 

1793  1803  v_sql TEXT; 

…  …  
1847  1857  

1848  1858  FOR rec IN 

1849   SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 

 1859  SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

 1860  (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 

1850  1861  FROM stratcon.rollup_matrix_numeric_20m 

1851  1862  WHERE date_hour(rollup_time)= v_min_whence 

…  …  
1854  1865  

1855  1866  INSERT INTO stratcon.rollup_matrix_numeric_60m 

1856   (sid,name,rollup_time,count_rows,avg_value) VALUES 

1857   (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 

 1867  (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 

 1868  (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 

1858  1869  

1859  1870  END LOOP; 

…  …  
1888  1899  

1889  1900  CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void 

1890   AS $$ 

1891   DECLARE 

 1901  AS $$DECLARE 

1892  1902  rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

1893  1903  v_sql TEXT; 

…  …  
1947  1957  

1948  1958  FOR rec IN 

1949   SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 

 1959  SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

 1960  (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 

1950  1961  FROM stratcon.rollup_matrix_numeric_60m 

1951  1962  WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'6 hour'::interval 

…  …  
1955  1966  

1956  1967  INSERT INTO stratcon.rollup_matrix_numeric_6hours 

1957   (sid,name,rollup_time,count_rows,avg_value) VALUES 

1958   (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 

 1968  (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 

 1969  (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 

1959  1970  

1960  1971  END LOOP; 
