Consider the "table-or-subquery" syntax chart linked below. https://www.sqlite.org/syntax/table-or-subquery.html
A modest proposal. In the "table-or-subquery" syntax there exists a branch for "table-function-name", aka table valued virtual tables. Why can't we have a parallel syntax branch for scalar valued "table-naming-function-name"? In other words, why not have support for simply naming an existing table or view by return value of a scalar function? There are very good reasons why this relatively simple change is a worthwhile improvement. 1. Since a named table or view is created by executing ordinary data definition SQL statements inside the table-naming-function, the dynamically named table/view and indexes can directly and fully participate in query optimization of the outer scope. 2. There are many dynamic table applications where a light wrapper on an ordinary table is all that is necessary. But, under the current API, one must write a lot of finicky optimizer hint code to expose a dynamically generated ordinary table through the requisite virtual table API. 3. I will speculate a bit. I see that DRH, with great surprise, very recently asked a participant on this forum why they are using such an old version of SQLite. Based on both the frequent need for ordinary but dynamic tables and the lack of a thin wrapper for such tables, there surely must be a lot of otherwise unnecessary "heavy" virtual table code in the wild. For credible production use, this heavy code must undergo a prohibitively time consuming requalification process after every engine update. Thus, it's no wonder that important SQLite applications cannot keep up with engine releases. I look forward to reading the forum comments on this modest proposal. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
