SQLite Forum

insert into view returning id returns null
Login

insert into view returning id returns null

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

I'm playing with insertable views and returning clause

Schema

	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 [link]

Working as expected, I think.

I wonder what would happen if <code>bar</code> 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

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.