SQLite Forum

insert into view returning id returns null

insert into view returning id returns null

(1) By happy-barney on 2021-07-24 14:35:37 [link] [source]

I'm playing with insertable views and returning clause


create table foo (id integer primary key, name text);

create view bar as select * from foo;

create trigger bar_insert
	instead of insert on bar for each row
	begin insert into foo (name) values (new.name); end

When executing insert into view (using snapshot - SQLite version 3.37.0 2021-07-19 14:00:29)

insert into bar (name) values ('a') returning id;

I get one row with one column, but its value is NULL.

Modification of data is successful otherwise, even when inserting into multiple tables.

(2) By Simon Slavin (slavin) on 2021-07-25 12:42:58 in reply to 1 [source]

Working as expected, I think.

I wonder what would happen if bar was a TABLE instead of a VIEW, but still had the same TRIGGER, which still didn't have a RETURNING clause.

Or suppose, inside of your TRIGGER, there were two INSERT commands. What would you expect to happen ?

(3) By happy-barney on 2021-07-25 14:23:46 in reply to 2 [link] [source]

expected behaviour? take all inserted rows, apply view in read mode on them, and return expression referring result of this read.

Multiple inserts in fact were primary reason why I tried this. Than magic with last_insert_rowid will be not sufficient enough.