That you for your kind comment about my table-naming-expression proposal.
Here is your suggestion with matched brackets and quotes and assuming
mytable has a column [tablename]:
select eval(printf('create table %s (a,b,c)',tablename)) from mytables;
Significant credit should also go to the built in printf function.
In general I've been thinking about materializing data dependent temporary
tables and even using them in CTE's. The tremendous expressive economy of
TCL and somewhat built-in support within SQLite got me thinking. Consider
the problem of pivot table function for creating temp.crosstab_* summary
tables...
-----------------------------------------
$
tclsh
%
load ./tcldbshell.so sqlite3
%
sqlite3 db :memory:
%
proc createpivotsum {table row column value} {
set outputtable "temp.crosstab\_$table\_$row\_$column\_$value";
set q [list "select $row"];
db eval "SELECT DISTINCT ($column)col FROM $table" cols {lappend q
"sum(case when $column='$cols(col)' then $value else 0 end)$cols(col)"};
db eval "DROP TABLE IF EXISTS $outputtable; CREATE TABLE $outputtable AS
[join $q ,] FROM $table GROUP BY $row;";
return $outputtable;
};
%
db function pivotsum -deterministic -argcount 4 createpivotsum;
%
#Wouldn't it be great if this TCL function could be called from the regular
shell?
%
#Standard libtclsqlite can't invoke shell.c yet so I added a shell call to
libtclsqlite.
%
db shell;
sqlite>
--Let's run a fruit stand.
sqlite>
drop table if exists sales;
create table sales(year integer,fruit text,tons integer);
insert into sales
values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7),(2017,'peaches',6),(2016,'mangos',12);
sqlite>
select * from sales;
year,fruit,tons
2017,mangos,3
2016,peaches,2
2017,apples,7
2017,peaches,6
2016,mangos,12
sqlite>
--What are current sales by year and type of fruit?
select pivotsum('sales','year','fruit','tons');
"pivotsum('sales','year','fruit','tons')"
temp.crosstab_sales_year_fruit_tons
sqlite>
select * from crosstab_sales_year_fruit_tons;
year,mangos,peaches,apples
2016,12,2,0
2017,3,6,7
sqlite>
--Try doing pivot stuff using in CTA noting that pivotsum is deterministic!
with t as (select (select pivotsum('sales','year','fruit','tons')),* from
crosstab_sales_year_fruit_tons)
...> select * from t;
Error: database table is locked
sqlite>
--So much for CTE's and deterministic column functions...
sqlite>
.exit
%
#now back in tclsh
%
exit
$
-----------------------------------------
For those that are interested I used slightly modified versions of
tclsqlite.c and shell.c to build libtclsqlite. About 10 lines changed
including the #ifndef TCL_DB_SHELL statements.
On Sun, Mar 26, 2017 at 10:36 AM, E.Pasma <pasm...@concepts.nl> wrote:
> 26-03-2017 petern :
>
> > The table-naming-expression, if
> > normal expressions are allowed, would obviously require sqlite3_prepare
> to
> > consult the database in situations where the name string expression
> depended
> > on a SQL statement being evaluated. Is this the main problem with
> allowing
> > dynamically named tables SQLite?
>
> Hi, in an earliar post you mentioned the loadable extension eval.c that
> adds
> the eval() SQL function. Just for my understanding, may I assume that you
> currently use that as a work around. E.g.
> select eval(printf("create table %s (a,b,c)', tablename) from mytables;
>
> Anyway thanks for mentioning the existense of eval(). E. Pasma
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 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
