MIME-Version: 1.0
In-Reply-To: 
 <CACKdPFhdUB0=9x2FTzkJ3_0xx6B2M5mTOoVRnCuFc7PjESLDqQ@mail.gmail.com>
References: 
 <CACKdPFikHUzyD2+eKhQRg-aVtL+is9uUzkBRCP+txhK=nURW_w@mail.gmail.com>
	<CACKdPFge5L5DM7UkF9GvOJd2U43fjdVoxdNhzy4PMsVtqMw1=g@mail.gmail.com>
	<CAK3UJRErzuGFQQOx0FzZV72dDdLBfd1P4baOD-Jmb2PF7AsCFQ@mail.gmail.com>
	<CACKdPFgTuvUFHoG-qhZh3UZY=JG8M5eMnL_q2gmWK_nh2xWoAg@mail.gmail.com>
	<CAK3UJREa0Dku2PnJACaLM6=_7OaQEqyPAs-_ZR=zwaDo2mwmAw@mail.gmail.com>
	<CACKdPFhdUB0=9x2FTzkJ3_0xx6B2M5mTOoVRnCuFc7PjESLDqQ@mail.gmail.com>
From: Josh Kupershmidt <schmiddy@gmail.com>
Date: Wed, 9 Nov 2011 20:44:38 -0500
Message-ID: 
 <CAK3UJRGB6NQ__MfCfiPi62WCsN5CKiXsgALZRzcuru_OisModw@mail.gmail.com>
Subject: Re: Partitionning + Trigger and Execute not working as expected
To: Sylvain Mougenot <smougenot@sqli.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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
> =A0 =A0 =A0 =A0currentTableName character varying :=3D 'job_' || '2011_11=
';
> BEGIN
> =A0 =A0 =A0 =A0EXECUTE 'INSERT INTO '|| currentTableName || ' values ' ||=
 (NEW.*);
> =A0 =A0 =A0 =A0RETURN NULL;
> END;
> $BODY$
> =A0LANGUAGE plpgsql VOLATILE
> =A0COST 100;
>
> CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo
> =A0FOR 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,)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0^
> QUERY: =A0INSERT INTO job_2011_11 values (10,)
> CONTEXT: =A0PL/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 :=3D '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