-- You must inport the `cube' contrib module to use this code

-- DROP TABLE IF EXISTS cubtest;
-- CREATE TABLE cubtest (c cube);
-- COMMENT ON TABLE cubtest IS 'Holds raw cube data import; lacks indexes';
-- \i output_vehicle.sql

CREATE LANGUAGE 'plpgsql';
DROP TABLE IF EXISTS cubtest2, sizes CASCADE;

CREATE TABLE cubtest2 (c cube);
CREATE INDEX t on cubtest2 using gist(c);
COMMENT ON TABLE cubtest2 IS 'Indexed table that is progressively added to during testing';

CREATE TABLE sizes (nrows INTEGER, tablesize INTEGER, totalsize INTEGER, indexsize INTEGER, bpr_row float, bpr_total float, bpr_index float);

CREATE OR REPLACE FUNCTION get_sizes(OUT nrows INTEGER, OUT tablesize_rows INTEGER, OUT tablesize_total INTEGER, OUT tablesize_index INTEGER, OUT bytes_per_row float, OUT bytes_per_row_total float, OUT bytes_per_row_index float) AS $$
BEGIN
	tablesize_rows = pg_relation_size('cubtest2');
	tablesize_total = pg_total_relation_size('cubtest2');
	tablesize_index = tablesize_total - tablesize_rows;
	nrows = (select count(*) from cubtest2);
	IF nrows <> 0 THEN
		bytes_per_row = tablesize_rows/ nrows;
		bytes_per_row_total = tablesize_total / nrows;
		bytes_per_row_index = tablesize_index / nrows;
	END IF;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION size_test() RETURNS SETOF sizes AS $$
DECLARE
	cubtest_rows INTEGER;
	inserted_rows INTEGER;
	o INTEGER;
BEGIN
	INSERT INTO sizes SELECT * FROM get_sizes();
	cubtest_rows = (SELECT count(*) FROM cubtest);
	INSERT INTO cubtest2 SELECT * FROM cubtest OFFSET 0 LIMIT 1;
	cubtest_rows = (SELECT count(*) FROM cubtest);
	inserted_rows = 1;
	WHILE inserted_rows < cubtest_rows LOOP
		INSERT INTO cubtest2 SELECT * FROM cubtest OFFSET inserted_rows LIMIT inserted_rows;
		INSERT INTO sizes SELECT * FROM get_sizes();
		inserted_rows = inserted_rows * 2;
	END LOOP;
	RETURN QUERY SELECT * FROM sizes;
END;
$$ LANGUAGE 'plpgsql';

SELECT * from size_test();