Re: Automating PostgreSql table partition using triggers

On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojović <viktor(dot)bojovic(at)gmail(dot)com>wrote:

> when creating dynamic query try to store it completey as string because you
> will not be able to change tableName (i havent been doing that for a long
> time , so possibly i can be wrong).
> to "exec" or "execute" command you have to pass the query as parameter.
> it would look like this
>
> declare sql as varchar;
> Field2=new. testing_name;
> Field1=new.testing_id;
> sql:='insert into tableName'||id||' (testing_id,testing_name) values
> ('||Field1||','||quote_literal(Field2)||')';
> exec(sql);
>
> On Thu, Jan 27, 2011 at 6:50 PM, Amitabh Kant <amitabhkant(at)gmail(dot)com>wrote:
>
>> Hi
>>
>> I am trying to write a function which is being called from a trigger used
>> for partitioning a large table. The partitioning is to happen based on an
>> integer field (testing_id). A simplified structure of what I am trying to do
>> is written below.
>>
>> Create Table tbltesting(
>> testing_id int not null,
>> testing_name character varying(255));
>>
>> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
>> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>>
>> CREATE OR REPLACE FUNCTION partition_insert_trigger()
>> RETURNS TRIGGER AS $$
>> DECLARE id integer ;
>> BEGIN
>> id := NEW.testing_id;
>>
>> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
>> sure what syntax to use here
>>
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>>
>> CREATE TRIGGER partition_trigger
>> BEFORE INSERT ON tbltesting
>> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>>
>> Creating tables or child tables is not a problem and the trigger works
>> fine if the function has static definitions. What I am trying to achieve is
>> use the new testing_id to create a table name for use in the insert
>> statement. If I am able to use the variable in the table name, I would not
>> have to re-declare the function each time with modified conditions for each
>> separate testing_id.
>>
>>
>> With regards
>>
>> Amitabh
>>
>> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me
>

Thanks Victor. This should work out for me.

Amitabh