INSERT RETURNING 与分区

使用 BEFORE INSERT 触发器进行分区的一种常见抱怨是，触发器中的 return NULL; 会阻止 INSERT … RETURNING 的正常工作。

一种解决方法是恢复使用 currval() 来查找插入的 ID；不幸的是，这仅适用于单行插入。另一种解决方法是让插入父表成功，但随后立即再次删除该行；这显然有缺点。

这是一个使用带有 INSTEAD 触发器的视图的替代解决方法的示例

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