Обсуждение: Re: Partitionning + Trigger and Execute not working as expected

Поиск
Список
Период
Сортировка

Re: Partitionning + Trigger and Execute not working as expected

От
Sylvain Mougenot
Дата:
Hello,
I'm trying to use table partitionning on a table called JOB.
Each month a new table is created to contain the rows created on that month.
ex : JOB_2011_11 for rows created during november 2011.

To do that I followed this advices on that page :
I also would like to create code dynamically into the trigger in order to have all "INSERT INTO" inheritated tables (tables like JOB_YYYY_MM) queries done.

But I can't make it work. I've an error when the insert is done using EXECUTE.
Working : INSERT INTO job_2011_11 values (NEW.*);
Not Woking : EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';

Could someone tell me how to make this EXECUTE work?
Thank you
Sylvain

Bellow is the full code (trigger) and error.
Code:
CREATE OR REPLACE FUNCTION job_insert_trigger()
  RETURNS trigger AS
$BODY$ 
DECLARE
currentTableName character varying := 'job_' ||to_char(NEW.datecreation,'YYYY_MM');
BEGIN
IF (NOT check_exist_table(currentTableName)) THEN
PERFORM add_table_job_yyyy_mm(currentTableName, NEW.datecreation);
END IF;

EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';
RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Error:
ERREUR: missing FROM clause for table « new »
SQL :42P01

Re: Partitionning + Trigger and Execute not working as expected

От
Josh Kupershmidt
Дата:
On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot <smougenot@sqli.com> wrote:
> EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';

The quotes in the above line are wrong; you want it like:

EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);

Josh

Re: Partitionning + Trigger and Execute not working as expected

От
Sylvain Mougenot
Дата:
Thank you for the help.
But it doesn't work :
EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);
QUERY:  INSERT INTO job_2011_11 values (117916386,-5,,2,2,1,,,,1,1,,0,,,,,,,,0,"2011-11-07 00:00:00","2011-11-07 00:00:00",,0,0,,0)
CONTEXT:  PL/pgSQL function "job_insert_trigger" line 9 at instruction EXECUTE

It looks like the NULL values are blanks in the generated query. 


Using the query below produces an error too
EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';
ERREUR: missing FROM clause for table « new »
SQL :42P01
QUERY:  INSERT INTO job_2011_11 values (NEW.*)
CONTEXT:  PL/pgSQL function "job_insert_trigger" line 9 at instruction EXECUTE


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)?

On Wed, Nov 9, 2011 at 1:35 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot <smougenot@sqli.com> wrote:
> EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';

The quotes in the above line are wrong; you want it like:

EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);

Josh



--
Sylvain Mougenot


Re: Partitionning + Trigger and Execute not working as expected

От
Josh Kupershmidt
Дата:
On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot <smougenot@sqli.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$
DECLAREcurrentTableName character varying := 'job_' || '2011_11';
BEGINEXECUTE '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

Re: Partitionning + Trigger and Execute not working as expected

От
Sylvain Mougenot
Дата:
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@gmail.com> wrote:
On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot <smougenot@sqli.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


Re: Partitionning + Trigger and Execute not working as expected

От
Josh Kupershmidt
Дата:
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