Re: dynamic partitioning - Mailing list pgsql-general

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