Hello,
I'm trying to use table partitionning on a table called JOB.
Each month a new table is created to contain the rows created on that month.
ex : JOB_2011_11 for rows created during november 2011.
To do that I followed this advices on that page :
I also would like to create code dynamically into the trigger in order to have all "INSERT INTO" inheritated tables (tables like JOB_YYYY_MM) queries done.
But I can't make it work. I've an error when the insert is done using EXECUTE.
Working : INSERT INTO job_2011_11 values (NEW.*);
Not Woking : EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';
Could someone tell me how to make this EXECUTE work?
Thank you
Sylvain
Bellow is the full code (trigger) and error.
Code:
CREATE OR REPLACE FUNCTION job_insert_trigger()
RETURNS trigger AS
$BODY$
DECLARE
currentTableName character varying := 'job_' ||to_char(NEW.datecreation,'YYYY_MM');
BEGIN
IF (NOT check_exist_table(currentTableName)) THEN
PERFORM add_table_job_yyyy_mm(currentTableName, NEW.datecreation);
END IF;
EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Error:
ERREUR: missing FROM clause for table « new »
SQL :42P01