BEGIN;

CREATE TEMPORARY TABLE
  mytemp(id serial, hit int, source_ip inet)
  ON COMMIT DROP;

INSERT INTO mytemp(hit,source_ip)
  SELECT count(*) AS counterhits, source_ip
  FROM firewall
  GROUP BY source_ip
  ORDER BY counterhits DESC;

CREATE OR REPLACE FUNCTION f_graph2() RETURNS text AS '
sql <- paste("SELECT id as x,hit as y FROM mytemp LIMIT 30",sep="");
str <- c(pg.spi.exec(sql));

mymain <- "Graph 2";
mysub <- paste("The worst offender is: ",str[1,3]," with ",str[1,2]," hits",sep="");
myxlab <- "Top 30 IP Addresses";
myylab <- "Number of Hits";

pdf(''/tmp/graph2.pdf'');
plot(str,type="b",main=mymain,sub=mysub,xlab=myxlab,ylab=myylab,lwd=3);
mtext("Probes by intrusive IP Addresses",side=3);
dev.off();

print(''DONE'');
' LANGUAGE plr;

-- now generating the graph
SELECT f_graph2();
COMMIT;