INSERT RETURNING vs Partitioning

A common complaint with the normal approach of using a BEFORE INSERT trigger for partitioning is that the return NULL; in the trigger prevents the normal functioning of INSERT … RETURNING from working.

One workaround is to revert to using currval() for finding inserted ids; this unfortunately only works for single-row inserts. Another workaround is to let the insert into the parent table succeed, but then immediately delete the row again; this has obvious drawbacks.

Here is an example of an alternative workaround, which uses a view with an INSTEAD trigger:

-- create a simple partitioned table

create table mydata_real (id serial, rowdate date, content text);
create table mydata_real_y2015 (check (rowdate >= '2015-01-01' and rowdate < '2016-01-01')) inherits (mydata_real);
create table mydata_real_y2016 (check (rowdate >= '2016-01-01' and rowdate < '2017-01-01')) inherits (mydata_real);

-- block attempts to insert on the actual parent table

create function mydata_nope() returns trigger language plpgsql
as $f$
  begin
    raise exception 'insert on wrong table';
    return NULL;
  end;
$f$;

create trigger mydata_nope before insert on mydata_real execute procedure mydata_nope();

-- create the view, which will be used for all access to the table:

create view mydata as select * from mydata_real;

-- need to copy any defaults from mydata_real to the view

alter view mydata alter column id set default nextval('mydata_real_id_seq');

-- this is the actual partition insert trigger:

create function mydata_partition() returns trigger language plpgsql
as $f$
  begin
    case extract(year from NEW.rowdate)
      when 2015
        then insert into mydata_real_y2015 select NEW.*;
      when 2016
        then insert into mydata_real_y2016 select NEW.*;
      else
        raise exception 'date % out of range', NEW.date;
    end case;
    return NEW;
  end;
$f$;

create trigger mydata_partition instead of insert on mydata
  for each row execute procedure mydata_partition();

-- sample insert

insert into mydata(rowdate,content)
  values ('2015-01-10','foo'),('2015-12-12','bar'),('2016-02-02','baz')
  returning id;