Re: Partitionning + Trigger and Execute not working as expected

Поиск
Список
Период
Сортировка
От Sylvain Mougenot
Тема Re: Partitionning + Trigger and Execute not working as expected
Дата
Msg-id CACKdPFhdUB0=9x2FTzkJ3_0xx6B2M5mTOoVRnCuFc7PjESLDqQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Partitionning + Trigger and Execute not working as expected  (Josh Kupershmidt <schmiddy@gmail.com>)
Ответы Re: Partitionning + Trigger and Execute not working as expected  (Josh Kupershmidt <schmiddy@gmail.com>)
Список pgsql-sql
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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Josh Kupershmidt
Дата:
Сообщение: Re: Partitionning + Trigger and Execute not working as expected
Следующее
От: Josh Kupershmidt
Дата:
Сообщение: Re: Partitionning + Trigger and Execute not working as expected