Thank you, Tom and Dan, for your constructive answers. To Pavel: My application reads the column types out in order to pretty print the values - as mentioned by Tom - but also to generate a dialog for entering new data (of course combined with INSTEAD OF TRIGGERs).
I understand that it's difficult to implement result-types for expressions in general. To me it would be enough if there would be at least one way to assign result-types with a CAST "wrapper" as I have mentioned before. Does anybody know whom to present this proposal in order remedy this current inconsistency in SQLite? sqlite-dev? In the meantime I thought of a hack and to assign the result-type by hand in the data dictionary after having created the VIEW. Would this be a feasible work around? Yours, S. 2010/5/6 BareFeetWare <list....@tandb.com.au>: > On 06/05/2010, at 2:51 PM, Dan Bishop wrote: > >> BareFeetWare wrote: >>> > >>> I've had the same issue. In the end I had to parse my view functions in my >>> own code and look for functions that give a particular type of result. So, >>> for instance, round() gives an integer, round(..., 2) gives a real, >>> concat() gives text. I also look for a cast(... as type) to use that >>> declared type. It's fiddly and I would have hoped SQLite would have at >>> least declared the type if a cast was present, but it seems not. >>> > >> A CAST expression could be assigned a declared type > > Yes, that should be fairly easy to do in SQLite, simpler than my adding it in > my own code (which then requires my own parser - not a trivial exercise). > >> but it would be difficult to implement it for expressions in general. Not >> only would you have to declare a type for every function > > Yes, probably half the functions don't have a consistently returned type (eg > length, concat). The ones that don't (eg coalesce) should just be left to > return a null (unknown) type. The schema developer can simply wrap those > expressions in a cast if they want a predetermined output type. > >> but you'd have to do it for operators as well, and what type should A * B be >> if A is "SMALLINT" and B is "UINT"? > > I hadn't thought of operators. As far as I know, mathematical operators (* / > + -) only work with integers and reals, Perhaps the result type could be set > to "numeric" or some other superclass. Or, to get real fancy, if the two > operands are declared integers, then the result is an integer; if one is real > then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite > as integer, so the result would be an integer. > >> Should relational operators have a declared type of BOOLEAN? > > Good idea. > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
