Re: Automating PostgreSql table partition using triggers - Mailing list pgsql-sql

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