drop function hostname();
--drop type stats_type;
--drop table stats;
create table stats2 (cnt integer,uri varchar(1536), cr varchar(1536));
--create table stats (cnt integer,uri varchar(1536), cr varchar(1536));
--create type stats_type as (cnt integer,uri varchar(1536), cr varchar(1536));
--insert into stats (cnt,uri,cr) select count(uri),uri,client_referer from apachelog group by uri, client_referer;
CREATE OR REPLACE FUNCTION hostname() RETURNS integer AS
'
DECLARE 
	row stats_type%ROWTYPE;
	rec record;
	newcr varchar(100);
BEGIN
   FOR rec IN SELECT * FROM stats ORDER BY cnt DESC
	 LOOP
		row.cnt = rec.cnt;
		SELECT INTO row.cr SUBSTRING(rec.cr FROM ''http://([^/]*).*'');
    IF row.cr IS NULL THEN
			 row.cr := ''localhost'';
    END IF;
		row.uri = rec.uri;
    insert into stats2 values (row.cnt, row.uri, row.cr);
	END LOOP;
	insert into stats2 values (row.cnt, row.uri, row.cr);
	RETURN 1;
END
'
LANGUAGE 'plpgsql';
select * from hostname();