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
