> What does sql say about this? I've found this line in SQL 2003: NOTE 268 — The addition of a column to a table has no effect on any existing <query expression> included in a view descriptor
> Any way I think that * as spec of column list must to be avoided by many > reasons as best practices suggest. Yes, to avoid possible problems it's better to be as explicit as possible. > But as I wrote this a question in my mind arose already. > What happens if the underlying table does not exist but FORCE option is > used? You can try ;) But I think that it will use column list from the first successful execution of query on that view when the table already exists. regards, S.Vladykin On 5 окт, 23:13, Rami Ojares <[email protected]> wrote: > Maybe the only change needed is to add something like this in CREATE > VIEW documentation. > > "If you use a star (*) in the select clause of the view definition the > star is evaluated at the creation time. > Adding/Removing column in the underlying table later does not affect the > view definition." > > But as I wrote this a question in my mind arose already. > What happens if the underlying table does not exist but FORCE option is > used? > > - Rami > > 5.10.2010 22:04, Dario Fassi kirjoitti: > > > Hi all, > > Sergei you are right and I'm outdated :-[ sorry. > > > Pgsql 8.4 , 9 and even the recent DB2 9.7 do that in the same way. DB2 > > continue storing "select * from " but return the table's column list at > > view's creation time. > > This behavior make sense because enable to dbms to store or cache > > optimized/native sql or even the prepared access plan for views. > > Any way I think that * as spec of column list must to be avoided by many > > reasons as best practices suggest. > > > So now would be inconsistent to maintain a view created code as "select *" > > and it would be advisable to replace the * with the list of columns that > > will be used effectively by the view. > > This way one would see in view's ddl what is actually executed. > > > regards, > > Dario > > > El 05/10/10 15:15, Sergi Vladykin escribió: > >> Hi, Dario. > > >> I can't say about almost all dbms but at least postgresql 9 works the > >> way h2 does. > >>> I think this might be criticized as some form of data loss. > >> I think the more important thing that view_defenition should always be > >> consistent with real view structure (query from view_defenition should > >> return the same result that query from the view), so if it will > >> contain wildcards then it should be rebuilded automatically each time > >> underlying table changes with respect to this changes. But for many > >> real applications it is undesirable behavior because views are > >> frequently targeted to be a stable interface to evolving underlying > >> table structure and changes in tables should not propagate on views. > > >> regards, > >> S.Vladykin > > >> On 5 окт, 20:34, Dario Fassi<[email protected]> wrote: > >>> Sergei, > >>> this behavior it's not documented and very confusing since almost all > >>> dbms conserve the original view's sql text and then * is always * (all > >>> columns). > > >>> There are other issue related to CREATE VIEW, it's that H2 store an > >>> optimized form of create view sql, and in complex view the stored sql > >>> text can be very different from the original. > >>> I think this might be criticized as some form of data loss. > >>> PostgreSQL, for example, also makes some cosmetic changes on the sql > >>> creation of a view, in change DB2 preserves the original text of view > >>> creation but internally runs an optimized version of it. > > >>> regards, > >>> Dario > > >>> El 05/10/10 03:33, Sergi Vladykin escribió: > > >>>> Hi, > >>>> This is a correct behavior. The view actually doesn't contain > >>>> wildcards, it contains all table columns for the moment of creation. > >>>> So if you want to add the column to the view too you have to > >>>> explicitly recreate it. > >>>> On 5 окт, 10:04, "[email protected]"<[email protected]> wrote: > >>>>> Hi, > >>>>> I wonder if followings is a correct behavior or a bug. > >>>>> sql> CREATE TABLE t (a INT,c INT); > >>>>> sql> CREATE VIEW tv AS SELECT * FROM t; > >>>>> sql> SELECT * FROM tv; > >>>>> A |C > >>>>> sql> ALTER TABLE t ADD COLUMN b INT BEFORE c; > >>>>> sql> SELECT * FROM t; > >>>>> A |B |C > >>>>> sql> SELECT * FROM tv; > >>>>> A |C > >>>>> "ALTER TABLE" query to the table that has views is successful, > >>>>> but the view's column (as determined by asterisk) are not changed. > >>>>> I've used version 1.2.141. > >>>>> Thank you. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
