Re: Partitionning + Trigger and Execute not working as expected

On Wed, Nov 9, 2011 at 4:39 PM, Sylvain Mougenot <smougenot(at)sqli(dot)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 VOLATILE
COST 100;

Josh