postgresql - Postgres Insert Into View Rule with Returning Clause -

i attempting allow insert statements returning clause view in postgres v9.4, struggling syntax. how want call insert statement:

create view myview select a.*, b.somecol1     tablea join tableb b using(aprimarykey); insert myview (time, somecol) values (sometime, somevalue) returning *; insert myview (somecol) values (somevalue) returning *; 

note default time now(). have far:

create rule myrulename on insert myview instead (     insert tablea (time) values coalesce(new.time, now());     insert tableb (aprimarykey, somecol)         values (currval('tablea_aprimarykey_seq'), new.somevalue); ); 

the above works insert value, struggling try , figure out how add returning statement. have tried following without success:

create rule myrulename on insert myview instead (     insert tablea (time) values coalesce(new.time, now())         returning *, new.somevalue;     insert tableb (aprimarykey, somecol)         values (currval('tablea_aprimarykey_seq'), new.somevalue); ); -- error:  invalid reference from-clause entry table "new"  create rule myrulename on insert myview instead (     (insert tablea (time)         values coalesce(new.time, now()) returning *)     insert tableb (aprimarykey, somecol)         select aprimarykey, new.somevalue returning *; ); -- error:  cannot refer new within query 

argh! know of way add returning statement gets primary key (serial) , time (timestamp time zone) added database in first insert, along value of somecol in second insert? thanks!

you better off using instead of insert trigger here:

create function myfuncname() returns trigger $$ declare   id integer; begin   insert tablea (time) values coalesce(new.time, now()) returning aprimarykey id;   insert tableb (aprimarykey, somecol1) values (id, new.somevalue);   return new; end; $$ language plpgsql;  create trigger myview_on_insert instead of insert on myview   each row execute procedure myfuncname(); 

checking current value of sequence see inserted in table bad bad bad practice. while here in single transaction, don't it.

you confused issue of returning information, because confused when read question. inside of function use into clause populate locally declared variables hold record values can use in subsequent statements. outside of function, use returning clause in top-most code snippet.