using PostgreSQL 8.4.5 on Ubuntu 10.04.1 LTS
I'm trying to create an INSERT trigger (plpgsql) based on the example provided here: http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html to automatically insert data into the currect yearly table partition.
For some reason, it puts double quotes on my timestamp values which causes the INSERT to fail. I'd rather not list all the NEW.columns, as i have 50+ columns and I'm hoping to use this function on several different tables that have completely different columns.
Data is imported like this:
INSERT INTO master VALUES ('2010-308 1455', 296.32,311,1.4732,.01182,1.6943,.32264,8.02,8.71,9.11,8.84,11.59,-.196,-.027,.227,.474,.011,.015,.006,.013,.007,.008,.024,.013,.016,.001,.001,296.7,311.6,1.486,.051,1.707,.367,8.05,8.72,9.13,8.86,11.61,-.194,-.025,295.4,309.1,1.433,-.012,1.682,.286,8,8.68,9.04,8.8,11.51,-.198,-.029);
The first column is a date/time which psql interprets correctly into a timestamp (I use this in several other scripts using non-partitioned tables that work fine).
For my non-partitioned tables, this works perfectly using a trigger with "INSERT INTO new_table SELECT NEW.*;" but not when I switch to a dynamic EXECUTE statement (see bellow)
No matter what I do, I can't get ride of the double quotes (or replace them with single quotes); see below for output. I've tried NEW.datetime = to_char( NEW.datetime,'YYYY-MM-DD HH24:MI:SS');
and
NEW.datetime = quote_nullable( NEW.datetime );
and
NEW.station = quote_literal(NEW.station);
and whatever else i could think of. What am I missing / doing wrong?
Function code :
--------------------------------
BEGIN
-- The table we'll inherit from
ourMasterTable := 'master';
-- Get the partition table names ~ master_year
SELECT ourMasterTable|| '_' || EXTRACT(ISOYEAR FROM NEW.datetime) into ourTable;
-- had to do this : EXECUTE will fail if i use NEW.* in ourInsertSTMT
SELECT NEW.* into new_row;
RAISE NOTICE '%',new_row;
-- Create our insert statement
ourInsertSTMT := 'INSERT INTO '|| ourTable || ' VALUES( ' || new_row || ')';
--Try execute it
EXECUTE ourInsertSTMT;
RETURN NULL;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM;
...
RAISE NOTICE 'Error inserting into existing partition % for %',ourTable,ourInsertSTMT;
END;
-------------------------------
result:
NOTICE: ("2010-11-04 14:55:00",296.32,311,1.4732,0.01182,1.6943,0.32264,8.02,8.71,9.11,8.84,11.59,-0.196,-0.027,0.227,0.474,0.011,0.015,0.006,0.013,0.007,0.008,0.024,0.013,0.016,0.001,0.001,296.7,311.6,1.486,0.051,1.707,0.367,8.05,8.72,9.13,8.86,11.61,-0.194,-0.025,295.4,309.1,1.433,-0.012,1.682,0.286,8,8.68,9.04,8.8,11.51,-0.198,-0.029)
NOTICE: NUM:42703, DETAILS:column "2010-11-04 14:55:00" does not exist
NOTICE: Error inserting into existing partition master_2010 for INSERT INTO master_2010 VALUES( ("2010-11-04 14:55:00", 296.32,311,1.4732,0.01182,1.6943,0.32264,8.02,8.71,9.11,8.84,11.59,-0.196,-0.027,0.227,0.474,0.011,0.015,0.006,0.013,0.007,0.008,0.024,0.013,0.016,0.001,0.001,296.7,311.6,1.486,0.051,1.707,0.367,8.05,8.72,9.13,8.86,11.61,-0.194,-0.025,295.4,309.1,1.433,-0.012,1.682,0.286,8,8.68,9.04,8.8,11.51,-0.198,-0.029))
