As I mentioned before, your code works on special cases (insert with all the columns) and those are very few cases.
Try this CREATE TABLE foo (a int, b int); CREATE TABLE job_2011_11 (c int, d int); CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' || '2011_11'; BEGIN EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); INSERT INTO foo (a, b) VALUES (1, 2); INSERT INTO foo (a) VALUES (10); ERROR: LINE 1: INSERT INTO job_2011_11 values (10,) ^ QUERY: INSERT INTO job_2011_11 values (10,) CONTEXT: PL/pgSQL function "job_insert_trigger" line 5 at instruction EXECUTE Regarding the self contained test for EXECUTE it's the same code. In the trigger the use of this code doesn't work : EXECUTE '*INSERT INTO job_2011_11 values (NEW.*)*'; but this one does work *INSERT INTO job_2011_11 values (NEW.*)*; So it looks like a trouble with EXECUTE to me! On Wed, Nov 9, 2011 at 8:25 PM, Josh Kupershmidt <schmi...@gmail.com> wrote: > On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot <smouge...@sqli.com> > wrote: > > > Even if the query below is fine (the exact content I try to build as a > > String to use with EXECUTE) > > INSERT INTO job_2011_11 values (NEW.*) > > Is there a way to solve this? > > Isn't it a bug (in how EXECUTE works)? > > I doubt this is a bug in EXECUTE; if you think it is, try to post a > self-contained test case. For example, this similar example works > fine: > > > CREATE TABLE foo (a int, b int); > CREATE TABLE job_2011_11 (c int, d int); > > CREATE OR REPLACE FUNCTION job_insert_trigger() > RETURNS TRIGGER AS > $BODY$ > DECLARE > currentTableName character varying := 'job_' || '2011_11'; > BEGIN > EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); > RETURN NULL; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > > CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo > FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); > INSERT INTO foo (a, b) VALUES (1, 2); > > > Josh > -- Sylvain Mougenot