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.
