On 11/05/2010, at 6:12 PM, Ben Harper wrote: > To determine the type of columns in a view I use > SELECT typeof(column) FROM viewname LIMIT something; > > Unfortunately if most of the column data is NULL then you can end up having > to scan the entire table.
Yes, I also do that as a last resort: 1. Check pragma table_info(MyView) to see if SQLite knows the column type. It seems to provide it only if the view's column directly references a table column. 2. If 1 returns null (unknown), then parse the schema of the view to look for the cast(expression, type) or other functions that return a known type. 3. If 2 fails to provide type through a known result type of a function, then go through the actual result data to see what types are returned. If all rows return the same type (ignoring null results) then use that type. Otherwise type is unknown (varies or all null). Of course this doesn't work if there are currently no rows in the view, which makes it impossible to determine the input type for the user if they choose to insert a new row. By the way, your select statement will scan the whole view. Limit just truncates the result, but SQLite is first scanning the whole thing. You'd be better served by something like: select typeof(ColumnName) as "Type" from ViewName where Type not null group by "Type"; If the result gives one row, then you have your type. If zero or multiple rows, then type could not be determined. As already mentioned, though, this won't help if the view currently has no rows, or if the value of MyColumn for the present rows is null, and is of limited value of there are just a few existing rows with data. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
