Re: Partitionning + Trigger and Execute not working as expected

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 <schmiddy(at)gmail(dot)com> wrote:

> On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot <smougenot(at)sqli(dot)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