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
partitioning_fake_data.sql
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
