Partitionning + Trigger and Execute not working as expected

From: Sylvain Mougenot <smougenot(at)sqli(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Partitionning + Trigger and Execute not working as expected
Date: 2011-11-08 09:03:11
Message-ID: CACKdPFikHUzyD2+eKhQRg-aVtL+is9uUzkBRCP+txhK=nURW_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2011-11-08 09:36:12 Re: Number timestamped rows
Previous Message Peter Eisentraut 2011-11-08 05:16:28 Re: the use of $$string$$