-- These functions provide the mechanism
-- for the 2nd step to preparing the data.
--
-- The only query that needs to be invoked
-- by the user is "SELECT f_firewall();".

BEGIN;

CREATE OR REPLACE FUNCTION f_parse(text,text) RETURNS text AS '
DECLARE
 i int := 1;
 word text := '' '';
 src text;
 proto text;
 spt text;
 dpt text;

BEGIN
 WHILE word<>'''' LOOP
  word := split_part($1,'' '',i);

  IF word<>'''' THEN
    IF word ~ ''SRC='' THEN
-- id the source ip address
      src := substring(word,5,20);

    ELSIF word ~ ''PROTO='' THEN
-- id the protocol
      proto := substring(word,7,20);

    ELSIF word ~ ''SPT='' THEN
-- id the source port
      spt := substring(word,5,20);

    ELSIF word ~ ''DPT='' THEN
-- id the destination port
      dpt := substring(word,5,20);

    END IF;
  END IF;
  i := i + 1;
 END LOOP;

-- insert data into one record
-- all fields are correctly cast
 INSERT INTO firewall
   	VALUES(
		timestamptz($2),
		inet(src),
		proto,
		int4(spt),
		int4(dpt)
		);
 RETURN null;
END;
' LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION f_firewall() returns text as '
DECLARE
rec record;
mydatestamp text;
myparse text;
BEGIN
  FOR rec IN SELECT * FROM master LOOP
    mydatestamp := ''2004'' || substring(rec.hit,0,16);
    myparse := substring(rec.hit,17,300);
    PERFORM f_parse(myparse,mydatestamp);
  END LOOP;
  RETURN ''DONE'';
END;
' LANGUAGE plpgsql;

SELECT f_firewall();
COMMIT;