INSERT trigger into partitioned table

Поиск
Список
Период
Сортировка
От Elford,Andrew [Ontario]
Тема INSERT trigger into partitioned table
Дата
Msg-id 33F9E32CDB0917428758DD583E747CC80DC11AE2@OntExch3.ontario.int.ec.gc.ca
обсуждение исходный текст
Список pgsql-general

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))

В списке pgsql-general по дате отправления:

Предыдущее
От: Chris Browne
Дата:
Сообщение: Re: Views - Under the Hood
Следующее
От: Raymond O'Donnell
Дата:
Сообщение: Re: Views - Under the Hood