Hi, Dario.
I can't say about almost all dbms but at least postgresql 9 works the
way h2 does. For example:
postgres=# create table t1 (id integer, name varchar(15));
CREATE TABLE
postgres=# create view v1 as select * from t1;
CREATE VIEW
postgres=# select * from v1;
id | name
----+------
(0 rows)
postgres=# alter table t1 add column price numeric(15,6);
ALTER TABLE
postgres=# select * from t1;
id | name | price
----+------+-------
(0 rows)
postgres=# select * from v1;
id | name
----+------
(0 rows)
postgres=# select view_definition from information_schema.views where
table_name='v1';
view_definition
--------------------------------
SELECT t1.id, t1.name FROM t1;
(1 row)
> 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.
