Hello list,
I'm currently thinking about a possibility to create a dynamic insert
statement inside a trigger function to put the data in the correct partition.
What am I talking about ?
I want to put data dependant on a timestamp column ("datetime") in seperate
partitions. Therefore I created the partitions, checks and a trigger function
with the following code:
""
...
date_part='';
date_part = to_char(NEW.datetime,'YYYY') || to_char(NEW.datetime,'MM');
tablename = 'table_' || date_part;
RAISE NOTICE 'target table: %', tablename;
EXECUTE 'insert into ' || tablename::regclass || ' values (NEW.*);';
--IF ( DATE (NEW.datetime) >= DATE '2010-11-01' AND
-- DATE (NEW.datetime) < DATE '2010-12-01' ) THEN
-- INSERT INTO tab_tour201011 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2010-12-01' AND
-- DATE (NEW.datetime) < DATE '2011-01-01' ) THEN
-- INSERT INTO tab_tour201012 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2011-01-01' AND
-- DATE (NEW.datetime) < DATE '2011-02-01' ) THEN
-- INSERT INTO tab_tour201101 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2011-02-01' AND
-- DATE (NEW.datetime) < DATE '2011-03-01' ) THEN
-- INSERT INTO tab_tour201102 VALUES (NEW.*);
...
""
The above code throws the following error while trying to insert data:
""
NOTICE: target table: table_201102
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into table_201102 values (NEW.*);
^
QUERY: insert into table_201102 values (NEW.*);
CONTEXT: PL/pgSQL function "insert_trigger" line 10 at EXECUTE statement
""
O.K., most probably this is caused by the fact that the statement "string"
includes the characters NEW, but not the values...or what....?!?!
The commented lines are working as expected and I think this is the common way
of handling partitions.
Now my question:
is it possible at all to create the insert statement on the fly, to avoid
modifying the trigger function each time a new partition has been added ?
any help appreciated....::GERD::....
