Partitioning dynamic table creation and Insertion

Dynamic Partition creation and insertion

CREATE TABLE footest (

foo_id integer NOT NULL,

blaa_id integer NOT NULL,

blaa_num integer NOT NULL,

foo_num integer NOT NULL,

createdatetime timestamp with time zone DEFAULT now()

);

CREATE TABLE footest_1to50k (

CHECK ( foo_id >= 1 AND foo_id <= 50000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_1to50k ON footest_1to50k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_1to50k ON footest_1to50k USING btree (foo_id, foo_num);

CREATE TABLE footest_51to100k (

CHECK ( foo_id >= 50001 AND foo_id <= 100000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_51to100k ON footest_51to100k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_51to100k ON footest_51to100k USING btree (foo_id, foo_num);

CREATE TABLE footest_101to150k (

CHECK ( foo_id >= 100001 AND foo_id <= 150000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_101to150k ON footest_101to150k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_101to150k ON footest_101to150k USING btree (foo_id, foo_num);

--===================CREATING TRIGGER & TRIGGER FUNCTION PARTITION TABLES========================================

CREATE TRIGGER insert_footest_trigger

BEFORE INSERT ON footest

FOR EACH ROW EXECUTE PROCEDURE footest_insert_trigger();

CREATE OR REPLACE FUNCTION footest_insert_trigger()

RETURNS trigger AS $$

DECLARE

part integer;

event_part1 integer;

event_part2 integer;

table_name varchar;

BEGIN

part = (((NEW.foo_id/50000))*50);

event_part1=0;

event_part2=0;

event_part1= part + 1;

event_part2= part + 50 ;

table_name='footest_' || event_part1 || 'to' || event_part2 || 'k';

IF not exists(select * from pg_class where relname = table_name) THEN

EXECUTE 'CREATE TABLE ' || table_name || '(CHECK ( foo_id >=' || event_part1 * 1000 || 'AND foo_id <=' || event_part2 * 1000 || ' )) INHERITS (footest)' ;

EXECUTE 'CREATE UNIQUE INDEX by_blaa_num_' || event_part1 || 'to' || event_part2 || 'k ON ' || table_name ||' USING btree (foo_id, blaa_id, blaa_num)';

EXECUTE 'CREATE UNIQUE INDEX pk_footest_' || event_part1 || 'to' || event_part2 || 'k ON ' || table_name ||' USING btree (foo_id, foo_num)';

EXECUTE 'GRANT ALL ON TABLE ' || table_name || ' TO foorole, postgres';

EXECUTE 'GRANT SELECT ON TABLE ' || table_name || ' TO blarole';

END IF;

EXECUTE 'INSERT INTO ' || table_name ||' VALUES (($1).*)' USING NEW ;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;