Sqlite view across multiple databases

sqlite, view

Solution

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';

Problem

Original source