On Wed, Nov 9, 2011 at 4:39 PM, Sylvain Mougenot <smougenot@sqli.com> wrote:
> 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
Oh, I see what you're on about now. Sounds like you're looking for the
USING clause of EXECUTE. Try this:
CREATE OR REPLACE FUNCTION job_insert_trigger()
RETURNS TRIGGER AS
$BODY$
DECLARE currentTableName character varying := 'job_' || '2011_11';
BEGIN EXECUTE 'INSERT INTO '|| currentTableName || ' (c, d) VALUES ($1, $2)' USING NEW.a, NEW.b;
RETURN NULL;
END;
$BODY$LANGUAGE plpgsql VOLATILECOST 100;
Josh