INSERT trigger into partitioned table

From: "Elford,Andrew [Ontario]" <Andrew(dot)Elford(at)ec(dot)gc(dot)ca>
To: <pgsql-general(at)postgresql(dot)org>
Subject: INSERT trigger into partitioned table
Date: 2010-11-04 20:28:20
Message-ID: 33F9E32CDB0917428758DD583E747CC80DC11AE2@OntExch3.ontario.int.ec.gc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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,29
6.7,311.6,1.486,.051,1.707,.367,8.05,8.72,9.13,8.86,11.61,-.194,-.025,29
5.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,1
1.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.19
6,-0.027,0.227,0.474,0.011,0.015,0.006,0.013,0.007,0.008,0.024,0.013,0.0
16,0.001,0.001,296.7,311.6,1.486,0.051,1.707,0.367,8.05,8.72,9.13,8.86,1
1.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))

Browse pgsql-general by date

  From Date Subject
Next Message Ivano Luberti 2010-11-04 20:43:49 Re: Views - Under the Hood
Previous Message Chris Browne 2010-11-04 19:58:35 Re: Views - Under the Hood