table_generator.sql

BEGIN;

-- CREATE LANGUAGE plpgsql;

-- _subject=1 only tables
-- _subject=2 only indices
-- _subject=3 tables and indices
-- _subject=4 only constraints
-- _subject=5 tables and constraints
-- _subject=6 indices and constraints
-- _subject=7 tables and indices and constraints
CREATE OR REPLACE FUNCTION create_sub_tables(_start date, _finish date, _subject integer) RETURNS SETOF text AS $$
DECLARE
	t_ret text;
	d date;
	t_date text;
	i integer;
	n integer;
	rn float;
	-- i_min integer;
	-- i_max integer;
	ts_min timestamptz;
	ts_max timestamptz;
BEGIN
	SET timezone TO utc;
	t_ret := '';
	n := 5;
	rn := 24 / ( n + 1 );
	FOR d IN
		SELECT x FROM generate_series(_start, _finish, '1 day'::interval) AS x
	LOOP
		t_date := to_char(d, 'YYYY') || '_' || to_char(d, 'MM') || '_' || to_char(d, 'DD');

		IF _subject & 1 > 0 THEN
			FOR i IN 0..n LOOP RETURN NEXT 'CREATE TABLE inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' () INHERITS (inh_parent);'; END LOOP;
		END IF;
		
		IF _subject & 2 > 0 THEN
			FOR i IN 0..n LOOP RETURN NEXT 'CREATE INDEX inh_child_' || t_date || '_idx1_' || trim(to_char(i, '00')) || ' ON inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' (timestamp1);'; END LOOP;
			FOR i IN 0..n LOOP RETURN NEXT 'CREATE INDEX inh_child_' || t_date || '_idx2_' || trim(to_char(i, '00')) || ' ON inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' (timestamp2);'; END LOOP;
			FOR i IN 0..n LOOP RETURN NEXT 'CREATE INDEX inh_child_' || t_date || '_idx3_' || trim(to_char(i, '00')) || ' ON inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' (timestamp3);'; END LOOP;
			FOR i IN 0..n LOOP RETURN NEXT 'CREATE INDEX inh_child_' || t_date || '_idx4_' || trim(to_char(i, '00')) || ' ON inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' (hashval, indexval DESC);'; END LOOP;
		END IF;

		IF _subject & 4 > 0 THEN
			FOR i IN 0..n LOOP
				-- i_min := extract(epoch FROM (d + ((i*rn)||' hours')::interval)::timestamptz);
				-- i_max := extract(epoch FROM (d + (((i+1)*rn)||' hours')::interval)::timestamptz);
				ts_min := d + ((i*rn)||' hours')::interval;
				ts_max := d + (((i+1)*rn)||' hours')::interval;
				RETURN NEXT 'ALTER TABLE inh_child_' || t_date || '_' || trim(to_char(i, '00')) || ' ADD CONSTRAINT tschk CHECK (timestamp1 >= '''||ts_min||'''::timestamptz AND timestamp1 < '''||ts_max||'''::timestamptz);';
			END LOOP;
		END IF;

	END LOOP;
	SET timezone TO default;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM create_sub_tables('2010-04-01', '2010-06-30', 7);

ROLLBACK;