Pragma table-valued functions used in views in an attached database.
Trying to create an attached information schema using the instructions
in the additional notes of the "Pragma functions" section of
https://www.sqlite.org/pragma.html I have come across an issue. I
execute the following statements in any sqlite database:
attach :memory: as info;
drop view if exists info.table_columns;
create view info.table_columns as
tbl_name as table_name,
c.cid as column_id,
c.name as column_name,
c."type" as "type",
c."notnull" as "notnull",
from sqlite_master m, pragma_table_info(m.tbl_name) c
where m.type = 'table';
Then, when I execute:
select * from table_columns;
select * from info.table_columns;
I get the error 'no such table: info.pragma_table_info'. But when I
execute the view's query directly I get the expected results. I get a
similar error if I try to use the example provided in the document:
CREATE VIEW info.indexed_columns AS
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_master AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
ORDER BY 1;
If I create the view in the main database it works as expected but I
would prefer to be able to create it in an attached memory database so
that it does not persist in the database file.
Am I doing something wrong/not supported?
One more note: I believe it is impossible to construct a result set
that contains all databases (main and attached) and all objects (e.g.
tables) within each database with a single SQL statement that works
without "knowledge" of the attached databases (else it is easy with a
union). If someone knows a way please let me know. If there isn't,
perhaps the addition of two more pragmas:
Re: Pragma table-valued functions used in views in an attached database.
On 12 Feb 2017, at 11:10am, Constantine Yannakopoulos <[hidden email]> wrote:
> On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin <[hidden email]> wrote:
>> You might have missed
>> which, despite its name, also works with views.
> No, this returns the columns of a single table. Suggested pragma
> table_list/view_list would return all tables/views in a database.
> I know I can get that from sqlite_master, but having to prefix it with
> the database name as part of the query syntax:
> select * from attached_db.sqlite_master;
> makes it impossible to join with pragma database_list:
> select * from pragma_database_list as dblist, dblist.name.sqlite_master;
Okay, I see what you mean. I don’t know of a way to do this in one command if you may have attached databases.