Postgresql: Insert trigger function fails on partial insert statement

Question

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?

Answer 1

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;

Answer 2

You already found that you have to use dynamic SQL with EXECUTE. What you are missing:

• If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways.

• You need to defend against SQL injection. Table names have to be treated as potentially dangerous user input.

• Don't concatenate values, pass them as values with the USING clause.

EXECUTE (format('
   INSERT INTO %I(created, modified, tr_status, ...)
   VALUES (($1).created, ($1).modified, ($1).tr_status, ...)'
   , child)
USING NEW;

Or, for many columns:

EXECUTE (format('
   INSERT INTO %I(created, modified, tr_status, ...)
   SELECT created, modified, tr_status, ...
   FROM  (SELECT ($1).*) t'
   , child)
USING NEW;

Related:

• Update multiple columns in a trigger function in plpgsql
• How to access NEW or OLD field given only the field's name?