Hi, 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 select tbl_name as table_name, c.cid as column_id, c.name as column_name, c."type" as "type", c."notnull" as "notnull", c.dflt_value, c.pk from sqlite_master m, pragma_table_info(m.tbl_name) c where m.type = 'table'; Then, when I execute: select * from table_columns; or 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 WHERE m.type='table' 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: pragma table_list(database-name); pragma view_list(database-name); would help. PS: I know all this is possible by creating eponymous virtual tables but the difficulty level for this is much higher. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
