From smougenot@sqli.com Tue Nov 8 09:08:35 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D543D18E7BF2 for ; Tue, 8 Nov 2011 05:08:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 60657-09 for ; Tue, 8 Nov 2011 09:08:25 +0000 (UTC) X-Greylist: delayed 00:05:12.144622 by SQLgrey-1.8.0-rc2 Received: from eu1sys200aog107.obsmtp.com (eu1sys200aog107.obsmtp.com [207.126.144.123]) by mail.postgresql.org (Postfix) with ESMTP id DBB5E18E7BEF for ; Tue, 8 Nov 2011 05:08:24 -0400 (AST) Received: from mail-ww0-f51.google.com ([74.125.82.51]) (using TLSv1) by eu1sys200aob107.postini.com ([207.126.147.11]) with SMTP; Tue, 08 Nov 2011 09:08:25 UTC Received: by mail-ww0-f51.google.com with SMTP id 27so241664wwf.32 for ; Tue, 08 Nov 2011 01:08:20 -0800 (PST) MIME-Version: 1.0 Received: by 10.180.93.168 with SMTP id cv8mr11851475wib.36.1320742991210; Tue, 08 Nov 2011 01:03:11 -0800 (PST) Received: by 10.180.106.135 with HTTP; Tue, 8 Nov 2011 01:03:11 -0800 (PST) Date: Tue, 8 Nov 2011 10:03:11 +0100 Message-ID: Subject: Partitionning + Trigger and Execute not working as expected From: Sylvain Mougenot To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=f46d043c7fbad4a6b104b1357041 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_FRT_BELOW2=0.01 X-Spam-Level: X-Archive-Number: 201111/53 X-Sequence-Number: 36026 --f46d043c7fbad4a6b104b1357041 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 : http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html 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 :=3D '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 =AB new =BB SQL :42P01 --f46d043c7fbad4a6b104b1357041 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
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 :
=
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
I also would like to create code dynamically into the trigger in order= to have all "INSERT INTO" inheritated tables (tables like JOB_YY= YY_MM) queries done.

But I can't make it work.= I've an error when the insert is done using EXECUTE.
Working :=A0INSERT INTO job_2011_11 values (NEW.*);
<= u>Not Woking :=A0EXECUTE 'INSERT INTO '|| currentTableName || &= #39; values (NEW.*)';

Could someone tell me ho= w to make this EXECUTE work?
Thank you
Sylvain

Bellow is the ful= l code (trigger) and error.
Code:
CREATE OR= REPLACE FUNCTION job_insert_trigger()
=A0 RETURNS trigger AS
$BODY$=A0
DECLARE
currentTableName character varying :=3D &#= 39;job_' ||to_char(NEW.datecreation,'YYYY_MM');
BEGIN=
IF (N= OT check_exist_table(currentTableName)) THEN
PERFORM add_table_job_yyyy_mm= (currentTableName, NEW.datecreation);
END I= F;

= EXECUTE 'INSERT INTO '|| currentTableName || ' values (= NEW.*)';
RETUR= N NULL;
END;
$BODY$
=A0 LANGUAGE plpgsql VOLA= TILE
=A0 COST 100;

Error:
ERREUR: missing FROM clause for table =AB new =BB
SQL := 42P01
--f46d043c7fbad4a6b104b1357041-- From smougenot@sqli.com Tue Nov 8 16:04:51 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7D6CE104804B for ; Tue, 8 Nov 2011 12:04:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 64768-08 for ; Tue, 8 Nov 2011 16:04:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from eu1sys200aog112.obsmtp.com (eu1sys200aog112.obsmtp.com [207.126.144.133]) by mail.postgresql.org (Postfix) with ESMTP id AC060104804C for ; Tue, 8 Nov 2011 12:04:44 -0400 (AST) Received: from mail-ww0-f50.google.com ([74.125.82.50]) (using TLSv1) by eu1sys200aob112.postini.com ([207.126.147.11]) with SMTP; Tue, 08 Nov 2011 16:04:44 UTC Received: by mail-ww0-f50.google.com with SMTP id 14so672290wwp.7 for ; Tue, 08 Nov 2011 08:04:39 -0800 (PST) MIME-Version: 1.0 Received: by 10.180.107.97 with SMTP id hb1mr14000767wib.18.1320768279819; Tue, 08 Nov 2011 08:04:39 -0800 (PST) Received: by 10.180.106.135 with HTTP; Tue, 8 Nov 2011 08:04:39 -0800 (PST) In-Reply-To: References: Date: Tue, 8 Nov 2011 17:04:39 +0100 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected From: Sylvain Mougenot To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=e89a8f2354f126359a04b13b54a3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_FRT_BELOW2=0.01 X-Spam-Level: X-Archive-Number: 201111/36 X-Sequence-Number: 36009 --e89a8f2354f126359a04b13b54a3 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 : http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html 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 :=3D '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 =AB new =BB SQL :42P01 --e89a8f2354f126359a04b13b54a3 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
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_YY= YY_MM) queries done.

But I can't make it work.= I've an error when the insert is done using EXECUTE.
Working :=A0INSERT INTO job_2011_11 values (NEW.*);
<= u>Not Woking :=A0EXECUTE 'INSERT INTO '|| currentTableName || &= #39; values (NEW.*)';

Could someone tell me ho= w to make this EXECUTE work?
Thank you
Sylvain

Bellow is the ful= l code (trigger) and error.
Code:
CREATE OR= REPLACE FUNCTION job_insert_trigger()
=A0 RETURNS trigger AS
$BODY$=A0
DECLARE
currentTableName character varying :=3D 'job_' ||to= _char(NEW.datecreation,'YYYY_MM');
BEGIN
IF (NOT check_exist_table(current= TableName)) THEN
PERFORM add_table_job= _yyyy_mm(currentTableName, NEW.datecreation);
END IF;

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

Error:
ERRE= UR: missing FROM clause for table =AB new =BB
SQL :42P01

--e89a8f2354f126359a04b13b54a3-- From schmiddy@gmail.com Wed Nov 9 00:35:59 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 150E775D0B1 for ; Tue, 8 Nov 2011 20:35:59 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 49992-05 for ; Wed, 9 Nov 2011 00:35:53 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-gx0-f174.google.com (mail-gx0-f174.google.com [209.85.161.174]) by mail.postgresql.org (Postfix) with ESMTP id 6BB1C75D0B0 for ; Tue, 8 Nov 2011 20:35:52 -0400 (AST) Received: by ggnb2 with SMTP id b2so1168392ggn.19 for ; Tue, 08 Nov 2011 16:35:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=KCYI71trGjJyvKtvXLpSFc5xXt3YjmxQp6XpPhDUYD4=; b=auYdhYQr19agPfbPjRmp/NsCv+Of5CJvp8Qwn5C7C4korb+MCkn737AaAALuJeKq2E hsAFbn7JeRyvpvPg9OvZ6K9+iS1gfk2bHhRz+Fej37FlQKC8MjVEFcsJRHZzTuldUqvo cjtHPolJtXEsfwyZmyzhNplr6iKnmGJ2XcZ40= Received: by 10.68.37.97 with SMTP id x1mr995023pbj.14.1320798952056; Tue, 08 Nov 2011 16:35:52 -0800 (PST) MIME-Version: 1.0 Received: by 10.68.66.170 with HTTP; Tue, 8 Nov 2011 16:35:31 -0800 (PST) In-Reply-To: References: From: Josh Kupershmidt Date: Tue, 8 Nov 2011 19:35:31 -0500 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected To: Sylvain Mougenot Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/41 X-Sequence-Number: 36014 On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot 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 From smougenot@sqli.com Wed Nov 9 11:57:43 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 379B515AE42B for ; Wed, 9 Nov 2011 07:57:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 35423-02 for ; Wed, 9 Nov 2011 11:57:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from eu1sys200aog110.obsmtp.com (eu1sys200aog110.obsmtp.com [207.126.144.129]) by mail.postgresql.org (Postfix) with ESMTP id 999AE15AE42A for ; Wed, 9 Nov 2011 07:57:35 -0400 (AST) Received: from mail-ey0-f169.google.com ([209.85.215.169]) (using TLSv1) by eu1sys200aob110.postini.com ([207.126.147.11]) with SMTP ID DSNKTrpqrpYN9e29Xc/OHBByqy9iuhzF5yON@postini.com; Wed, 09 Nov 2011 11:57:36 UTC Received: by mail-ey0-f169.google.com with SMTP id 4so1534718eye.0 for ; Wed, 09 Nov 2011 03:57:34 -0800 (PST) MIME-Version: 1.0 Received: by 10.180.87.199 with SMTP id ba7mr2422421wib.27.1320839854433; Wed, 09 Nov 2011 03:57:34 -0800 (PST) Received: by 10.180.106.135 with HTTP; Wed, 9 Nov 2011 03:57:34 -0800 (PST) In-Reply-To: References: Date: Wed, 9 Nov 2011 12:57:34 +0100 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected From: Sylvain Mougenot To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=f46d0444e9395424a904b14bfe40 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/44 X-Sequence-Number: 36017 --f46d0444e9395424a904b14bfe40 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 =AB new =BB 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 wrote= : > On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot > 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 > --=20 Sylvain Mougenot --f46d0444e9395424a904b14bfe40 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thank you for the help.
But it doesn't work :
EXECUTE = 9;INSERT INTO '|| currentTableName || ' values ' || (NEW.*);
QUERY: =A0INSERT I= NTO job_2011_11 values (117916386,-5,,2,2,1,,,,1,1,,0,,,,,,,,0,"2011-1= 1-07 00:00:00","2011-11-07 00:00:00",,0,0,,0)
CONTEXT: =A0PL/pgSQL funct= ion "job_insert_trigger" line 9 at instruction EXECUTE

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


Using the query below produces an error = too
EXECUTE 'INSERT INTO '|| currentTableName || ' va= lues (NEW.*)';
ERREUR: missing FROM clause for= table =AB new =BB
SQL :42P01
QUERY: =A0INSER= T INTO job_2011_11 values (NEW.*)
CONTEXT: =A0PL/pgSQL function "job_insert_trigger&= quot; line 9 at instruction EXECUTE


Even if the query below is fine (t= he 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, Jos= h Kupershmidt <s= chmiddy@gmail.com> wrote:
On Tue, Nov 8, 2011 at 11= :04 AM, Sylvain Mougenot <smougeno= t@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



--
Sylva= in Mougenot


--f46d0444e9395424a904b14bfe40-- From schmiddy@gmail.com Wed Nov 9 19:27:57 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 142BA15D1310 for ; Wed, 9 Nov 2011 15:27:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 98807-01-8 for ; Wed, 9 Nov 2011 19:27:50 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com [209.85.160.174]) by mail.postgresql.org (Postfix) with ESMTP id 20DC31C263D3 for ; Wed, 9 Nov 2011 15:25:29 -0400 (AST) Received: by gyc15 with SMTP id 15so2071826gyc.19 for ; Wed, 09 Nov 2011 11:25:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=0RoDQKAD+sc+CrQr8XRD6faS/TbL6TRBM/Pjxuof/Rg=; b=R2rVxp3F30VHmkpBEwnNdiZwF5RyO3IfxNdnmglDEdvMiiB79tH2R7zDA7DQQqYnP8 EqHgQVBQQ/+sRVwl2HI5l5KshL8lxX4v8cTK5oi8/Ok+x4Z2oKNBFSuXITh7YBZx8Xg9 5QpVInF4+7CnLtnrbswjTSv5cgzhKECyIXanE= Received: by 10.68.64.169 with SMTP id p9mr7198592pbs.116.1320866726064; Wed, 09 Nov 2011 11:25:26 -0800 (PST) MIME-Version: 1.0 Received: by 10.68.66.170 with HTTP; Wed, 9 Nov 2011 11:25:05 -0800 (PST) In-Reply-To: References: From: Josh Kupershmidt Date: Wed, 9 Nov 2011 14:25:05 -0500 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected To: Sylvain Mougenot Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/45 X-Sequence-Number: 36018 On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot 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 From smougenot@sqli.com Wed Nov 9 21:40:58 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id CEACC106B0AF for ; Wed, 9 Nov 2011 17:40:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 92693-01-7 for ; Wed, 9 Nov 2011 21:40:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from eu1sys200aog107.obsmtp.com (eu1sys200aog107.obsmtp.com [207.126.144.123]) by mail.postgresql.org (Postfix) with ESMTP id 7C142FCF6DB for ; Wed, 9 Nov 2011 17:39:59 -0400 (AST) Received: from mail-ww0-f52.google.com ([74.125.82.52]) (using TLSv1) by eu1sys200aob107.postini.com ([207.126.147.11]) with SMTP ID DSNKTrrzLudtunUGxZAOz3VEmzQBKe9ekbXc@postini.com; Wed, 09 Nov 2011 21:40:01 UTC Received: by wwn31 with SMTP id 31so3573391wwn.9 for ; Wed, 09 Nov 2011 13:39:58 -0800 (PST) MIME-Version: 1.0 Received: by 10.180.3.33 with SMTP id 1mr4950652wiz.54.1320874798404; Wed, 09 Nov 2011 13:39:58 -0800 (PST) Received: by 10.180.106.135 with HTTP; Wed, 9 Nov 2011 13:39:58 -0800 (PST) In-Reply-To: References: Date: Wed, 9 Nov 2011 22:39:58 +0100 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected From: Sylvain Mougenot To: Josh Kupershmidt Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=f46d041390ad26d2e904b15421e6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_FONT_FACE_BAD=0.981, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/46 X-Sequence-Number: 36019 --f46d041390ad26d2e904b15421e6 Content-Type: text/plain; charset=ISO-8859-1 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 wrote: > On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot > 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 --f46d041390ad26d2e904b15421e6 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable As I mentioned before, your code works on special cases (insert with all th= e columns) and those are very few cases.

Try this=A0

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 =A0currentTableN= ame character varying :=3D 'job_' || '2011_11';
BEGIN
=A0 =A0 =A0 =A0EXECUTE 'INSERT INTO '|| currentTableName || ' v= alues ' || (NEW.*);
=A0 =A0 =A0 =A0RETURN NU= LL;
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);=A0

ERROR:
L= INE 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



Regarding the self contained test for E= XECUTE it's the same code.
In the trigger the use of this cod= e doesn't work :
EXECUTE 'INSERT INTO job_2011_11 values (NEW.*)= 9;;
but
this one does work
INSERT INTO job_2011_11 values (NEW.*);

<= div>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:<= br>
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
=A0 =A0 =A0 =A0currentTableName character varying :=3D 'job_' || &= #39;2011_11';
BEGIN
=A0 =A0 =A0 =A0EXECUTE 'INSERT INTO '|| currentT= ableName || ' 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);


Josh



--
Sylva= in Mougenot


--f46d041390ad26d2e904b15421e6-- From schmiddy@gmail.com Thu Nov 10 01:45:09 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 25E2E3B9519 for ; Wed, 9 Nov 2011 21:45:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 75775-06 for ; Thu, 10 Nov 2011 01:45:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id A01C13B9517 for ; Wed, 9 Nov 2011 21:45:00 -0400 (AST) Received: by yenr9 with SMTP id r9so1331811yen.19 for ; Wed, 09 Nov 2011 17:44:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=URa3i8aB4ps7SBkYuDg3lI6xsTx+lDAAXOncTtrgeLk=; b=uhFhMQbW2sIdi/ysV+2O4dEwuVtVIyeb66RZ/Ps2c7LD+MwkodyVVJ2Oj3L/E8+CPE nj1Rm76TJXGo3HwOK9EG+hKDoSLrjuyuyvF4jfe/tDOsnL5uiG9ZG2oOGhjSyQdyMoQ4 ZfKFRpG83Hm6hM+6bUlOTOqcybKBQW5864Mak= Received: by 10.68.20.233 with SMTP id q9mr5071255pbe.99.1320889499039; Wed, 09 Nov 2011 17:44:59 -0800 (PST) MIME-Version: 1.0 Received: by 10.68.66.170 with HTTP; Wed, 9 Nov 2011 17:44:38 -0800 (PST) In-Reply-To: References: From: Josh Kupershmidt Date: Wed, 9 Nov 2011 20:44:38 -0500 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected To: Sylvain Mougenot Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201111/47 X-Sequence-Number: 36020 On Wed, Nov 9, 2011 at 4:39 PM, Sylvain Mougenot 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 From jasen@xnet.co.nz Sat Nov 12 10:01:56 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5DAD71F22147 for ; Sat, 12 Nov 2011 06:01:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 22322-06 for ; Sat, 12 Nov 2011 10:01:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 4377C1F22146 for ; Sat, 12 Nov 2011 06:01:48 -0400 (AST) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1RPAPD-00062w-6n for pgsql-sql@postgresql.org; Sat, 12 Nov 2011 11:01:47 +0100 Received: from ip-118-90-23-26.xdsl.xnet.co.nz ([118.90.23.26]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sat, 12 Nov 2011 11:01:47 +0100 Received: from jasen by ip-118-90-23-26.xdsl.xnet.co.nz with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sat, 12 Nov 2011 11:01:47 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Jasen Betts Subject: Re: Partitionning + Trigger and Execute not working as expected Date: 12 Nov 2011 09:56:02 GMT Organization: Dis (not Dat) Organisation Lines: 34 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: ip-118-90-23-26.xdsl.xnet.co.nz X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-18 (Linux) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.101 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201111/57 X-Sequence-Number: 36030 On 2011-11-08, Sylvain Mougenot wrote: > --f46d043c7fbad4a6b104b1357041 > Content-Type: text/plain; charset=ISO-8859-1 > Content-Transfer-Encoding: quoted-printable > > 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 : > http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html > 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? EXECUTE 'INSERT INTO '|| currentTableName || ' select ('||quote_literal(NEW)||'::job%ROWTYPE).*'; or EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; -- ⚂⚃ 100% natural From smougenot@sqli.com Wed Nov 16 12:01:31 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 58C1EF3C962 for ; Wed, 16 Nov 2011 08:01:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 44518-05 for ; Wed, 16 Nov 2011 12:01:24 +0000 (UTC) X-Greylist: delayed 00:06:21.477459 by SQLgrey-1.8.0-rc2 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 1F09DF3C963 for ; Wed, 16 Nov 2011 08:01:24 -0400 (AST) Received: from eu1sys200aog110.obsmtp.com ([207.126.144.129]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RQe4x-000097-LK for pgsql-sql@postgresql.org; Wed, 16 Nov 2011 11:55:01 +0000 Received: from mail-ww0-f46.google.com ([74.125.82.46]) (using TLSv1) by eu1sys200aob110.postini.com ([207.126.147.11]) with SMTP ID DSNKTsOkhvA/409wrm8xBiRXDssZJC6Zge0G@postini.com; Wed, 16 Nov 2011 11:54:59 UTC Received: by wwe3 with SMTP id 3so340759wwe.27 for ; Wed, 16 Nov 2011 03:54:46 -0800 (PST) MIME-Version: 1.0 Received: by 10.180.87.199 with SMTP id ba7mr35607965wib.27.1321444485805; Wed, 16 Nov 2011 03:54:45 -0800 (PST) Received: by 10.180.106.135 with HTTP; Wed, 16 Nov 2011 03:54:45 -0800 (PST) Date: Wed, 16 Nov 2011 12:54:45 +0100 Message-ID: Subject: Re: Partitionning + Trigger and Execute not working as expected From: Sylvain Mougenot To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=f46d0444e9392ab61304b1d8c58c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/68 X-Sequence-Number: 36041 --f46d0444e9392ab61304b1d8c58c Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable It works with the answer suggested by Jasen Betts EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; Thank you all for all the help, and special thanks to Josh Kupershmidt and Jasen Betts (in the order I received messages) The full code is --------------------------- SQL ---------------------- -- Main table DROP TABLE IF EXISTS job; CREATE TABLE job ( idjob serial NOT NULL, idjobclient character varying(64) NOT NULL, idclient integer NOT NULL, idmode integer, datecreation timestamp without time zone NOT NULL ); -- Inherited table DROP TABLE IF EXISTS job_2011_11; CREATE TABLE job_2011_11 ( CONSTRAINT job_2011_11_check_datecreation CHECK (datecreation >=3D '2011-11-01 00:00:00'::timestamp without time zone AND datecreation < '2011-12-01 00:00:00'::timestamp without time zone) ) INHERITS (job); -- Trigger to insert in the good table CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying :=3D 'job_' ||to_char(NEW.datecreation,'YYYY_MM'); BEGIN -- Automate table creation --IF (NOT check_exist_table(currentTableName)) THEN -- PERFORM add_table_job_yyyy_mm(currentTableName, NEW.datecreation); --END IF; EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER job_insert_trg BEFORE INSERT ON job FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); -- Try to insert datas INSERT INTO job (idjob, idjobclient, idclient, datecreation) VALUES (1, '2', 3, '2011-11-16 00:00:00.0'); --------------------------- SQL ---------------------- > ---------- Forwarded message ---------- > From: Jasen Betts > To: pgsql-sql@postgresql.org > Date: 12 Nov 2011 09:56:02 GMT > Subject: Re: Partitionning + Trigger and Execute not working as expected > On 2011-11-08, Sylvain Mougenot wrote: > > --f46d043c7fbad4a6b104b1357041 > > Content-Type: text/plain; charset=3DISO-8859-1 > > Content-Transfer-Encoding: quoted-printable > > > > 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=3D > > . > > ex : JOB_2011_11 for rows created during november 2011. > > > > To do that I followed this advices on that page : > > http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html > > 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? > > EXECUTE 'INSERT INTO '|| currentTableName || ' select > ('||quote_literal(NEW)||'::job%ROWTYPE).*'; > > or > > EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW; > > > -- > =E2=9A=82=E2=9A=83 100% natural > > Sylvain Mougenot --f46d0444e9392ab61304b1d8c58c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable It works with the answer suggested by=C2=A0Jasen Betts
EXECUTE 'INS= ERT INTO '|| currentTableName || ' values ($1.*)' USING NEW;
Thank you all for all the help,=C2=A0
and special thanks to= =C2=A0Josh Kupershmidt and=C2=A0Jasen Betts (in the order I received messag= es)

The full code is=C2=A0

--------------------------- SQL ---------= -------------
-- Main table
DROP TABLE IF EXISTS j= ob;
CREATE TABLE job (
=C2=A0 idjob serial NOT NULL,
=C2=A0 idjobclient character varying(64) NOT NULL,
=C2=A0 idclien= t integer NOT NULL,
=C2=A0 idmode integer,
=C2=A0 datec= reation timestamp without time zone NOT NULL
);

-- Inherited table
DROP TABLE IF EXISTS job_2011_11;
CREATE TABLE job_2011_11
(
=C2=A0 CONSTRAINT job_2011_11_check_datecreation CHECK= (datecreation >=3D '2011-11-01 00:00:00'::timestamp without tim= e zone AND datecreation < '2011-12-01 00:00:00'::timestamp witho= ut time zone)
)
INHERITS (job);

-- Trigger to ins= ert in the good table
CREATE OR REPLACE FUNCTION job_insert_trigg= er()
RETURNS TRIGGER AS
$BODY$
DECLARE
curre= ntTableName character varying :=3D 'job_' ||to_char(NEW.datecreatio= n,'YYYY_MM');
BEGIN
-- Automate table creation
--IF = (NOT check_exist_table(currentTableName)) THEN
-- PERFORM add_table_job_yyyy_mm(curren= tTableName, NEW.datecreation);
--END= IF;
=

EXECUTE 'INSERT INTO '|| currentTableName || ' values= ($1.*)' USING NEW;
RETUR= N NULL;
END;
$BODY$
=C2=A0LANGUAGE plpgsql VO= LATILE
=C2=A0COST 100;

CREATE TRIGGER jo= b_insert_trg BEFORE INSERT ON job
=C2=A0FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger();
= =C2=A0
-- Try to insert datas
INSERT INTO job (idjob, i= djobclient, idclient, datecreation) VALUES (1, '2', 3, '2011-11= -16 00:00:00.0');
--------------------------- SQL ----------------------

<= /div>
=
---------- Forwarded message ----------
From:=C2=A0Jasen Betts <jasen@xnet.co.nz>
To:=C2=A0pgsql-sql@postgresql.org
Date:= =C2=A012 Nov 2011 09:56:02 GMT
Subject:=C2=A0Re: Partitionning + Trigger and Execute not working as expect= ed
On 2011-11-08, Sylvain Mougenot <smougenot@sqli.com> wrote:
> --f46d043c7fbad4a6b104b1357041
> Content-Type: text/plain; charset=3DISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> 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=3D
> .
> ex : JOB_2011_11 for rows created during november 2011.
>
> To do that I followed this advices on that page :
> http://www.postgresql.org/docs/9.1/static/ddl-parti= tioning.html
> I also would like to create code dynamically into the trigger in order= to
> have all "INSERT INTO" inheritated tables (tables like JOB_Y= YYY_MM) queries
> done.
>
> But I can't make it work. I've an error when the insert is don= e using
> EXECUTE.
> *Working :* INSERT INTO job_2011_11 values (NEW.*);
> *Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || &#= 39; values
> (NEW.*)';
>
> Could someone tell me how to make this EXECUTE work?

EXECUTE 'INSERT INTO '|| currentTableName || ' select ('||q= uote_literal(NEW)||'::job%ROWTYPE).*';

or

EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)= 9; USING NEW;


--
=E2=9A=82=E2=9A=83 100% natural

Sylvain Mougenot<= br>

--f46d0444e9392ab61304b1d8c58c--