I’ve updated SQLite3 to version 3.27.2, and the previous error has gone away. However, the docs are still lying, because information_schema.tables cannot be implemented (evidence below). It seems that we need a pragma_table_list() function, which takes a schema as a parameter (similar to pragma_table_info(), pragma_index_list(), etc.)
Joshuas-MBP:sqldark josh$ sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> ATTACH ':memory:' AS 'information_schema'; sqlite> CREATE VIEW information_schema.tables(table_name) AS ...> SELECT name FROM main.sqlite_master ...> WHERE type = 'table'; Error: view tables cannot reference objects in database main > On Feb 27, 2019, at 2:48 PM, Joshua Wise <joshuathomasw...@gmail.com> wrote: > > Ahh that’s very interesting. I suspect this is the relevant entry in the > release notes: > > SQLite Release 3.26.0 On 2018-12-01 > Enhanced triggers so that they can use table-valued functions that exist in > schemas other than the schema where the trigger is defined. > > Although, we’re talking about views here, not triggers. > > >> On Feb 27, 2019, at 2:43 PM, David Raymond <david.raym...@tomtom.com >> <mailto:david.raym...@tomtom.com>> wrote: >> >> For me it gives an error up through 3.25.3 and starts working at 3.26.0 >> >> >> >> -----Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org >> <mailto:sqlite-users-boun...@mailinglists.sqlite.org>] On Behalf Of Joshua >> Wise >> Sent: Wednesday, February 27, 2019 2:13 PM >> To: SQLite mailing list >> Subject: Re: [sqlite] Documentation is lying to me >> >> I can confirm that it also doesn’t work with SQLite version 3.24.0 on a >> completely different machine. >> >>> On Feb 27, 2019, at 9:23 AM, Joshua Thomas Wise <joshuathomasw...@gmail.com >>> <mailto:joshuathomasw...@gmail.com>> wrote: >>> >>> Joshuas-MBP:sqldark josh$ sqlite3 >>> SQLite version 3.25.0 2018-09-15 04:01:47 >>> Enter ".help" for usage hints. >>> Connected to a transient in-memory database. >>> Use ".open FILENAME" to reopen on a persistent database. >>> sqlite> SELECT sqlite_version(); >>> 3.25.0 >>> sqlite> ATTACH ':memory:' AS 'information_schema'; >>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS >>> ...> SELECT name FROM pragma_database_list(); >>> sqlite> select * from schemata; >>> Error: no such table: information_schema.pragma_database_list >>> sqlite> select * from pragma_database_list(); >>> 0|main| >>> 2|information_schema| >>> >>> It worked when using `pragma_database_list()` directly, but not from inside >>> the view. >>> >>> >>> >>>> On Feb 26, 2019, at 9:26 PM, Keith Medcalf <kmedc...@dessus.com >>>> <mailto:kmedc...@dessus.com> <mailto:kmedc...@dessus.com >>>> <mailto:kmedc...@dessus.com>>> wrote: >>>> >>>> >>>> Unable to reproduce: >>>> >>>>> sqlite >>>> SQLite version 3.28.0 2019-02-25 18:43:54 >>>> Enter ".help" for usage hints. >>>> Connected to a transient in-memory database. >>>> Use ".open FILENAME" to reopen on a persistent database. >>>> sqlite> ATTACH ':memory:' AS 'information_schema'; >>>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS >>>> ...> SELECT name FROM pragma_database_list(); >>>> sqlite> >>>> sqlite> select * from schemata; >>>> main >>>> information_schema >>>> sqlite> .exit >>>> >>>> What version of SQLite are you using? >>>> What do the following commands do? >>>> >>>> pragma database_list; >>>> >>>> select * from pragma_database_list(); >>>> >>>> >>>> --- >>>> The fact that there's a Highway to Hell but only a Stairway to Heaven says >>>> a lot about anticipated traffic volume. >>>> >>>>> -----Original Message----- >>>>> From: sqlite-users [mailto:sqlite-users- >>>>> boun...@mailinglists.sqlite.org <mailto:boun...@mailinglists.sqlite.org> >>>>> <mailto:boun...@mailinglists.sqlite.org >>>>> <mailto:boun...@mailinglists.sqlite.org>>] On Behalf Of Joshua Thomas Wise >>>>> Sent: Tuesday, 26 February, 2019 19:06 >>>>> To: sqlite-users@mailinglists.sqlite.org >>>>> <mailto:sqlite-users@mailinglists.sqlite.org> >>>>> <mailto:sqlite-users@mailinglists.sqlite.org >>>>> <mailto:sqlite-users@mailinglists.sqlite.org>> >>>>> Subject: [sqlite] Documentation is lying to me >>>>> >>>>> In this SQLite3 documentation >>>>> (https://www.sqlite.org/pragma.html#pragfunc >>>>> <https://www.sqlite.org/pragma.html#pragfunc> >>>>> <https://www.sqlite.org/pragma.html#pragfunc >>>>> <https://www.sqlite.org/pragma.html#pragfunc>>), it says that >>>>> information_schema could be implemented by doing something like this: >>>>> >>>>> ATTACH ':memory:' AS 'information_schema'; >>>>> CREATE VIEW information_schema.schemata(schema_name) AS >>>>> SELECT name FROM pragma_database_list(); >>>>> >>>>> However, when attempting to do this, we get an error: "no such table: >>>>> information_schema.pragma_database_list”. >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> sqlite-users@mailinglists.sqlite.org >>>>> <mailto:sqlite-users@mailinglists.sqlite.org> >>>>> <mailto:sqlite-users@mailinglists.sqlite.org >>>>> <mailto:sqlite-users@mailinglists.sqlite.org>> >>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >>>> >>>> >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@mailinglists.sqlite.org >>>> <mailto:sqlite-users@mailinglists.sqlite.org> >>>> <mailto:sqlite-users@mailinglists.sqlite.org >>>> <mailto:sqlite-users@mailinglists.sqlite.org>> >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >>> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> <mailto:sqlite-users@mailinglists.sqlite.org> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> <mailto:sqlite-users@mailinglists.sqlite.org> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
