From: | Sylvain Mougenot <smougenot(at)sqli(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Partitionning + Trigger and Execute not working as expected |
Date: | 2011-11-08 09:03:11 |
Message-ID: | CACKdPFikHUzyD2+eKhQRg-aVtL+is9uUzkBRCP+txhK=nURW_w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 :
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2011-11-08 09:36:12 | Re: Number timestamped rows |
Previous Message | Peter Eisentraut | 2011-11-08 05:16:28 | Re: the use of $$string$$ |