Re: dynamic partitioning

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 <dzaf88(at)gmail(dot)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
>