On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant <amitabhk...@gmail.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 > > Forgot to add that I am using 8.4. With regards Amitabh