It works with the answer suggested by Jasen Betts EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW;
Thank you all for all the help, and special thanks to Josh Kupershmidt and Jasen Betts (in the order I received messages) The full code is --------------------------- SQL ---------------------- -- Main table DROP TABLE IF EXISTS job; CREATE TABLE job ( idjob serial NOT NULL, idjobclient character varying(64) NOT NULL, idclient integer NOT NULL, idmode integer, datecreation timestamp without time zone NOT NULL ); -- Inherited table DROP TABLE IF EXISTS job_2011_11; CREATE TABLE job_2011_11 ( CONSTRAINT job_2011_11_check_datecreation CHECK (datecreation >= '2011-11-01 00:00:00'::timestamp without time zone AND datecreation < '2011-12-01 00:00:00'::timestamp without time zone) ) INHERITS (job); -- Trigger to insert in the good table CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' ||to_char(NEW.datecreation,'YYYY_MM'); BEGIN -- Automate table creation --IF (NOT check_exist_table(currentTableName)) THEN -- PERFORM add_table_job_yyyy_mm(currentTableName, NEW.datecreation); --END IF; EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER job_insert_trg BEFORE INSERT ON job FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); -- Try to insert datas INSERT INTO job (idjob, idjobclient, idclient, datecreation) VALUES (1, '2', 3, '2011-11-16 00:00:00.0'); --------------------------- SQL ---------------------- > ---------- Forwarded message ---------- > From: Jasen Betts <ja...@xnet.co.nz> > To: pgsql-sql@postgresql.org > Date: 12 Nov 2011 09:56:02 GMT > Subject: Re: Partitionning + Trigger and Execute not working as expected > On 2011-11-08, Sylvain Mougenot <smouge...@sqli.com> wrote: > > --f46d043c7fbad4a6b104b1357041 > > Content-Type: text/plain; charset=ISO-8859-1 > > Content-Transfer-Encoding: quoted-printable > > > > 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? > > EXECUTE 'INSERT INTO '|| currentTableName || ' select > ('||quote_literal(NEW)||'::job%ROWTYPE).*'; > > or > > EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; > > > -- > ⚂⚃ 100% natural > > Sylvain Mougenot