Postgresql: Insert trigger function fails on partial insert statement

insertpostgresqltrigger

We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test db and explain everything to the rest later. I'm no dba, I'm a perl developer.

Focused on just one master table, I've produced a function to create a new children table, to create children tables for the existing information and to move things from master to children by batches. I don't think it's relevant, but our checks are on the year and month of the created field.

Now I'm building the insert trigger in the master database that would detect the period for the new record, look for the corresponding children table, creates it if missing, and inserts. Everything is ready but the insert is not working!

Our inserts statements are usually partial: will add the basic fields, do some things and fill in the rest (the fill in part will come later). So the insert statement is something like

INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');

and leave Postgres to fill in the id. But in between created and tr_status, the schema has a field called modified. And, when the data gets to the trigger function, this field is empty. Trying to insert the row will throw an error:

EXECUTE (
    'INSERT INTO ' || child || ' VALUES ' || NEW.* 
);
LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed...
                                                             ^

How can I use in the trigger the same fields that were in the insert statement?

Best Answer

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;