On 2016-04-06 6:00 AM, Cezary H. Noweta wrote: > On 2016-04-06 09:43, Darren Duncan wrote: >> On 2016-04-05 10:19 AM, Richard Hipp wrote: > >>> It seems to me that the most consistent answer is that the "type" of >>> columns in a VIEW should always be an empty string. > >> That's only consistent if you do the same thing with base tables. > > Non--consistency: > > sqlite> CREATE TABLE a (a DECIMAL(10)); > sqlite> CREATE TABLE b AS SELECT * FROM a; > sqlite> CREATE VIEW bv AS SELECT * FROM a; > sqlite> PRAGMA table_info(b); > 0|a|NUM|0||0 > sqlite> PRAGMA table_info(bv); > 0|a||0||0 > > Info on column's affinity (besides if it is a declared one --- DECIMAL, FLOAT > --- or a real one --- NUMERIC, REAL) is still useful, when comparison is made. > > Column's affinity is still propagated: > > sqlite> CREATE TABLE b2 AS SELECT * FROM bv; > sqlite> PRAGMA table_info(b2); > 0|a|NUM|0||0 > > so the point is that ``PRAGMA table_info'' stopped displaying column's > affinity > in case of views.
I agree with you in the sense that CREATE TABLE AS SELECT and CREATE VIEW AS SELECT with the same SELECT should produce results with identical type info. > <snip> > >> Alternately, you can exploit the explicit column list that is optional >> in a CREATE VIEW: >> >> CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2; > > Syntax error. Did you mean: > > CREATE VIEW v3 (foo) AS SELECT CAST(w+x+y+z AS NUMERIC) FROM t2; I actually meant what I said, wherein the column list is declared using the same syntax as one would use for a CREATE TABLE, meaning with types. However, your use of explicit CAST syntax is possibly a better solution in one sense, in that the SELECT itself is completely unambiguous to human readers what the result type is. It also fits right in with what I think is the best solution, that column type info just is derived from the SELECT expression. -- Darren Duncan
