sql - Postgres trigger-based insert redirection without breaking RETURNING -

i'm using table inheritance in postgres, trigger i'm using partition data child tables isn't quite behaving right. example, query returns nil, return id of new record.

insert flags (flaggable_id, flaggable_type)  values (233, 'thank')  returning id; 

if change return value of trigger function null new, desired returning behavior, 2 identical rows inserted in database. makes sense, since non-null return value trigger function causes original insert statement execute, whereas returning null causes statement halt execution. unique index might halt second insertion, raise error.

any ideas how make insert returning work trigger this?

create table flags (   id integer not null,   flaggable_type character varying(255) not null,   flaggable_id integer not null,   body text );  alter table flags     add constraint flags_pkey primary key (id);  create table "comment_flags" (  check ("flaggable_type" = 'comment'),  primary key ("id"),  foreign key ("flaggable_id") references "comments"("id") ) inherits ("flags");  create table "profile_flags" (  check ("flaggable_type" = 'profile'),  primary key ("id"),  foreign key ("flaggable_id") references "profiles"("id") ) inherits ("flags");  create or replace function flag_insert_trigger_fun() returns trigger $body$ begin   if (new."flaggable_type" = 'comment')     insert comment_flags values (new.*);   elsif (new."flaggable_type" = 'profile')     insert profile_flags values (new.*);   else     raise exception 'wrong "flaggable_type"="%", fix flag_insert_trigger_fun() function', new."flaggable_type";   end if;   return null; end; $body$ language plpgsql;  create trigger flag_insert_trigger   before insert on flags   each row execute procedure flag_insert_trigger_fun(); 

the workaround found, create view base table & use instead of triggers on view:

create table flags_base (     id integer not null,     flaggable_type character varying(255) not null,     flaggable_id integer not null,     body text );  alter table flags_base     add constraint flags_base_pkey primary key (id);  create table "comment_flags" (  check ("flaggable_type" = 'comment'),  primary key ("id") ) inherits ("flags_base");  create table "profile_flags" (  check ("flaggable_type" = 'profile'),  primary key ("id") ) inherits ("flags_base");  create or replace view flags select * flags_base;  create or replace function flag_insert_trigger_fun() returns trigger $body$ begin   if (new."flaggable_type" = 'comment')     insert comment_flags values (new.*);   elsif (new."flaggable_type" = 'profile')     insert profile_flags values (new.*);   else     raise exception 'wrong "flaggable_type"="%", fix flag_insert_trigger_fun() function', new."flaggable_type";   end if;   return new; end; $body$ language plpgsql;  create trigger flag_insert_trigger   instead of insert on flags   each row execute procedure flag_insert_trigger_fun(); 

but way must supply id field on each insertion (even if flags_base's primary key has default value / serial), must prepare insert trigger fix new.id if null.

update: seems views' columns can have default values too, set with

 alter view [ if exists ] name alter [ column ] column_name set default expression 

which used in views have insert/update rule/trigger.

http://www.postgresql.org/docs/9.3/static/sql-alterview.html