one note: I create a table of 100 entries in order to test it so I want 5 partition of 20 entries each. (And not a table of 100000 entries)
thanks again! dafni On Wed, Jun 26, 2013 at 4:47 PM, dafNi zaf <dza...@gmail.com> wrote: > Hello! > > I want to dynamically create partition tables that inherit a main table > called "foo". > The creation must occur when needed. > > For example, lets say that I want to insert 100000 entries and I want 5 > partition > tables (with 20000 entries each). > > So, first I need a partition for the first 20000 entries and when the > entries reach > the number 20000, another partition must be created, e.t.c.. > > I guess I need something like that: > > --the main table is: > > CREATE TABLE foo ( > 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() > ); > > --and the trigger function is: > > CREATE OR REPLACE FUNCTION foo_insert_trigger() > RETURNS trigger AS $$ > DECLARE > entry_id integer; > from_value integer; > to_value integer; > table_name varchar; > BEGIN > entry_id = NEW.foo_id; > from_value = entry_id + 1; > to_value = entry_id + 20; > table_name='foo_' || from_value || '_to_' || to_value; > > IF not exists(select * from pg_class where relname = table_name) THEN > EXECUTE 'CREATE TABLE ' || table_name || '(CHECK ( foo_id >=' || > from_value || 'AND foo_id <=' || to_value || ' )) INHERITS (foo)' ; > EXECUTE 'CREATE UNIQUE INDEX by_blaa_num_' || from_value || '_to_' || > to_value || 'k ON ' || table_name ||' USING btree (foo_id, blaa_id, > blaa_num)'; > EXECUTE 'CREATE UNIQUE INDEX pk_foo_' || from_value || '_to_' || > to_value || '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; > > > but it doesn't seem to work. It doesn't actually create new partition > tables. > The entries are inserted into "foo" > > I attach a test .sql file that contains the data of the table > > > any help would save me from a lot of time! > > thank you in advance! > > dafni >
