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.
