BEGIN;

CREATE TEMPORARY TABLE mytemp(counter int,source_ip inet) ON COMMIT DROP;
CREATE TEMPORARY TABLE mytemp1(totalcount int, hr int DEFAULT 0) ON COMMIT DROP;

CREATE OR REPLACE FUNCTION t_graph3() returns text AS '
DECLARE
BEGIN

-- LOOP HERE THROUGH ALL 24 HOURS
FOR i IN 1..24 LOOP
  INSERT INTO mytemp(counter,source_ip)
    SELECT COUNT(*) AS counter,source_ip
    FROM firewall WHERE int4(to_char(d_stamp,''HH24''))=i
    GROUP BY source_ip;

  INSERT INTO mytemp1(totalcount) SELECT sum(counter) FROM mytemp;
  UPDATE ONLY mytemp1 SET hr = i WHERE hr = 0;
  TRUNCATE mytemp;
END LOOP;

return ''DONE'';
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_graph3() RETURNS text AS '
sql <- paste("SELECT hr as timeofday, totalcount as connectionattempts FROM mytemp1",sep="");
str <- pg.spi.exec(sql);

mymain <- "Graph 3";
myxlab <- "Time: 24/Hours";
myylab <- "Number of Hits";

pdf(''/tmp/graph3.pdf'');
plot(str,type="b",main=mymain,xlab=myxlab,ylab=myylab,lwd=2);
mtext("Accumulated scan of the entire database over a 24 hour period",side=3);
dev.off();
print(''DONE'');
' LANGUAGE plr;


SELECT t_graph3();
SELECT f_graph3();

COMMIT;