Sqlite view across multiple databases

Question

Is it possible to create a VIEW (not temporary view) in a Sqlite database that has other databases attached to it? The view should be able to access data from all databases via joined tables.

Answer 1

No, the view must be temporary, otherwise an error will occur:

sqlite> create view view1 as select * from db2.foo union select * from main.foo;
Error: view view1 cannot reference objects in database db2
sqlite> create temp view view1 as select * from db2.foo union select * from main.foo;
sqlite> select * from view1;
...

This makes sense since a temporary view is part of the automatically created temp database which only exists for the current process.

EDIT:

You can list the temporary tables and views (all stored in the automatically created temp database) this way:

sqlite> .headers on
sqlite> select * from sqlite_temp_master;
type|name|tbl_name|rootpage|sql
view|view1|view1|0|CREATE VIEW view1 as select * from db2.foo union select * from main.foo

To list views only:

select * from sqlite_temp_master where type='view';