pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitionning + Trigger and Execute not working as expected
9+ messages / 3 participants
[nested] [flat]
* Partitionning + Trigger and Execute not working as expected
@ 2011-11-08 09:03 Sylvain Mougenot <smougenot@sqli.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sylvain Mougenot @ 2011-11-08 09:03 UTC (permalink / raw)
To: pgsql-sql
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 := '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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-08 16:04 Sylvain Mougenot <smougenot@sqli.com>
parent: Sylvain Mougenot <smougenot@sqli.com>
1 sibling, 1 reply; 9+ messages in thread
From: Sylvain Mougenot @ 2011-11-08 16:04 UTC (permalink / raw)
To: pgsql-sql
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 := '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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-09 00:35 Josh Kupershmidt <schmiddy@gmail.com>
parent: Sylvain Mougenot <smougenot@sqli.com>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Kupershmidt @ 2011-11-09 00:35 UTC (permalink / raw)
To: Sylvain Mougenot <smougenot@sqli.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-09 11:57 Sylvain Mougenot <smougenot@sqli.com>
parent: Josh Kupershmidt <schmiddy@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sylvain Mougenot @ 2011-11-09 11:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-09 19:25 Josh Kupershmidt <schmiddy@gmail.com>
parent: Sylvain Mougenot <smougenot@sqli.com>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Kupershmidt @ 2011-11-09 19:25 UTC (permalink / raw)
To: Sylvain Mougenot <smougenot@sqli.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-09 21:39 Sylvain Mougenot <smougenot@sqli.com>
parent: Josh Kupershmidt <schmiddy@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sylvain Mougenot @ 2011-11-09 21:39 UTC (permalink / raw)
To: Josh Kupershmidt <schmiddy@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-10 01:44 Josh Kupershmidt <schmiddy@gmail.com>
parent: Sylvain Mougenot <smougenot@sqli.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Kupershmidt @ 2011-11-10 01:44 UTC (permalink / raw)
To: Sylvain Mougenot <smougenot@sqli.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-12 09:56 Jasen Betts <jasen@xnet.co.nz>
parent: Sylvain Mougenot <smougenot@sqli.com>
1 sibling, 0 replies; 9+ messages in thread
From: Jasen Betts @ 2011-11-12 09:56 UTC (permalink / raw)
To: pgsql-sql
On 2011-11-08, Sylvain Mougenot <smougenot@sqli.com> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partitionning + Trigger and Execute not working as expected
@ 2011-11-16 11:54 Sylvain Mougenot <smougenot@sqli.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sylvain Mougenot @ 2011-11-16 11:54 UTC (permalink / raw)
To: pgsql-sql
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 >=
'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 := '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 <jasen@xnet.co.nz>
> 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 <smougenot@sqli.com> 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
>
> Sylvain Mougenot
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-11-16 11:54 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-08 09:03 Partitionning + Trigger and Execute not working as expected Sylvain Mougenot <smougenot@sqli.com>
2011-11-08 16:04 ` Sylvain Mougenot <smougenot@sqli.com>
2011-11-09 00:35 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-11-09 11:57 ` Sylvain Mougenot <smougenot@sqli.com>
2011-11-09 19:25 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-11-09 21:39 ` Sylvain Mougenot <smougenot@sqli.com>
2011-11-10 01:44 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-11-12 09:56 ` Jasen Betts <jasen@xnet.co.nz>
2011-11-16 11:54 Re: Partitionning + Trigger and Execute not working as expected Sylvain Mougenot <smougenot@sqli.com>
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