Re: dynamic partitioning

You forgot to set the trigger on foo:

CREATE TRIGGER foo_insert
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();

2013/6/26 dafNi zaf <dzaf88(at)gmail(dot)com>

> 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
>>
>
>

--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル