From: Josh Kupershmidt <schmiddy@gmail.com>
To: Sylvain Mougenot <smougenot@sqli.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Partitionning + Trigger and Execute not working as expected
Date: Wed, 9 Nov 2011 20:44:38 -0500
Message-ID: <CAK3UJRGB6NQ__MfCfiPi62WCsN5CKiXsgALZRzcuru_OisModw@mail.gmail.com> (raw)
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>
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 VOLATILE
COST 100;
Josh
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: schmiddy@gmail.com, smougenot@sqli.com
Subject: Re: Partitionning + Trigger and Execute not working as expected
In-Reply-To: <CAK3UJRGB6NQ__MfCfiPi62WCsN5CKiXsgALZRzcuru_OisModw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox