Обсуждение: SELECT * in a CREATE VIEW statement doesn't update column set automatically
This isn't exactly a bug, but it could be considered unintuitive behavior. Consider this: CREATE VIEW foo AS SELECT * FROM a; CREATE VIEW foo_v AS SELECT * FROM foo; ALTER TABLE foo ADD COLUMN b INT; The ALTER TABLE statement affects VIEW foo, but the column addition does not propagate to VIEW foo_v. Thus, it makes this deceptive: ... AS SELECT * FROM foo; I ran into this with an application where a real table is accessed if the user is an "admin", while regular users access a view instead. I considered "AS SELECT * FROM foo" to be a promise that all columns from foo would be included in the view, but the promise is broken when ADD COLUMN is applied later on. Would it be a desirable feature to make `CREATE VIEW foo_v AS SELECT * FROM foo;` automatically update the column set when foo's columns change? Instead of the wildcard * being expanded once at CREATE VIEW time, it would (semantically) be expanded every time foo_v is selected on.
Joseph Adams <joeyadams3.14159@gmail.com> writes: > This isn't exactly a bug, but it could be considered unintuitive > behavior. It's required by the SQL standard. regards, tom lane
Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically
От
Merlin Moncure
Дата:
On Thu, May 6, 2010 at 3:01 PM, Joseph Adams <joeyadams3.14159@gmail.com> wrote: > This isn't exactly a bug, but it could be considered unintuitive > behavior. Consider this: by unintuitive you mean: 'explicitly defined in the SQL standard' :-).I happen to agree with you but that's irrelevant. If you absolutely require this use the composite type workaround. merlin
Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically
От
Andrew Dunstan
Дата:
Tom Lane wrote: > Joseph Adams <joeyadams3.14159@gmail.com> writes: > >> This isn't exactly a bug, but it could be considered unintuitive >> behavior. >> > > It's required by the SQL standard. > > > And many places regard "select *" in anything other than throw-away queries as bad practice anyway. I have seen people get bitten by it over and over again, and I have worked at companies where it is explicitly forbidden in coding standards. cheers andrew
Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically
От
Merlin Moncure
Дата:
On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > And many places regard "select *" in anything other than throw-away queries > as bad practice anyway. I have seen people get bitten by it over and over > again, and I have worked at companies where it is explicitly forbidden in > coding standards. In terms of application queries I generally agree. However, I think this rule does not apply to server side definitions, especially in regards to views and/or composite types. There are cases where you _want_ the view to be define as 'all fields of x'...In fact, it's pretty typical IMNSHO. It may be possible to expose this behavior. I'd like to see: select * from foo -- and -- select (foo).* exhibit different behaviors -- ().* is more a type operator, returning all the fields of foo, than a field list expression. This gives us a cool loophole to exploit for views that really want to be defined with *: create view particular_foos as select (foo).* from foo where something = true; create view something_complex as select (foo).*, (func(foo.field)).*; -- execute func() just one time please! The something_complex case above is a real problem in how it behaves currently -- sometimes without a hassle free workaround. Am I off my rocker? :-) I've made this point many times (prob got annoying a long time ago) but I'm curious if you guys agree... merlin
On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: > On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> And many places regard "select *" in anything other than throw-away queries >> as bad practice anyway. I have seen people get bitten by it over and over >> again, and I have worked at companies where it is explicitly forbidden in >> coding standards. > > In terms of application queries I generally agree. However, I think > this rule does not apply to server side definitions, especially in > regards to views and/or composite types. There are cases where you > _want_ the view to be define as 'all fields of x'...In fact, it's > pretty typical IMNSHO. It may be possible to expose this behavior. > > I'd like to see: > select * from foo > -- and -- > select (foo).* > exhibit different behaviors -- ().* is more a type operator, returning > all the fields of foo, than a field list expression. This gives us a > cool loophole to exploit for views that really want to be defined with > *: > create view particular_foos as select (foo).* from foo where something = true; > create view something_complex as select (foo).*, (func(foo.field)).*; > -- execute func() just one time please! > > The something_complex case above is a real problem in how it behaves > currently -- sometimes without a hassle free workaround. Am I off my > rocker? :-) I've made this point many times (prob got annoying a long > time ago) but I'm curious if you guys agree... What you're suggesting makes sense to me. What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegantsolution than drop and re-create the view. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically
От
Merlin Moncure
Дата:
On Mon, May 17, 2010 at 2:15 PM, Jim Nasby <decibel@decibel.org> wrote: > On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: >> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> And many places regard "select *" in anything other than throw-away queries >>> as bad practice anyway. I have seen people get bitten by it over and over >>> again, and I have worked at companies where it is explicitly forbidden in >>> coding standards. >> >> In terms of application queries I generally agree. However, I think >> this rule does not apply to server side definitions, especially in >> regards to views and/or composite types. There are cases where you >> _want_ the view to be define as 'all fields of x'...In fact, it's >> pretty typical IMNSHO. It may be possible to expose this behavior. >> >> I'd like to see: >> select * from foo >> -- and -- >> select (foo).* >> exhibit different behaviors -- ().* is more a type operator, returning >> all the fields of foo, than a field list expression. This gives us a >> cool loophole to exploit for views that really want to be defined with >> *: >> create view particular_foos as select (foo).* from foo where something = true; >> create view something_complex as select (foo).*, (func(foo.field)).*; >> -- execute func() just one time please! >> >> The something_complex case above is a real problem in how it behaves >> currently -- sometimes without a hassle free workaround. Am I off my >> rocker? :-) I've made this point many times (prob got annoying a long >> time ago) but I'm curious if you guys agree... > > What you're suggesting makes sense to me. > > What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegantsolution than drop and re-create the view. Well, the workaround I was specifically talking about was dealing with the problem of composite type return from functions executing the function multiple times: select (func()).*; This gets expanded to select func().f1, func().f2, etc. This is the behavior I think has to go. if func returns foo and foo has 6 columns, func gets executed 6 times for each row. The workaround is this: select (q).f.* from (select func() as f) q; the problem here is that forcing the function call into a subquery can be awkward in non trival queries -- it causes other problems. What you are probably looking for is to be able to add columns to a view without recreating it: create table foo(...); create view v as select foo from foo; now you can just do: select (foo).* from v; small disclaimer: I don't actually do this much, it might cause other issues. postgres is pretty smart about detecting how composite type changes cascade to other structures. This is an exception! postgres=# create table foo(a int, b int, c int); CREATE TABLE postgres=# create view v as select foo from foo; CREATE VIEW postgres=# create view vv as select (v).foo.c; CREATE VIEW postgres=# insert into foo select 1,2,3; INSERT 0 1 postgres=# insert into foo select 2,4,6; INSERT 0 1 alter table foo drop column c; -- uh oh ALTER TABLE postgres=# select * from v; -- this seems ok foo -------(1,2)(2,4) postgres=# select * from vv; -- urk! postgres=# \d+ vv View "public.vv"Column | Type | Modifiers | Storage | Description --------+---------+-----------+---------+-------------c | integer | | plain | View definition:SELECT (v.foo)."........pg.dropped.3........" AS c FROM v; I don't actually mind this so much TBH...feature not bug. I hesitated fixing this because I was terrified someone might actually fix it. merlin
Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically
От
Merlin Moncure
Дата:
On Mon, May 17, 2010 at 2:15 PM, Jim Nasby <decibel@decibel.org> wrote: > On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: >> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> And many places regard "select *" in anything other than throw-away queries >>> as bad practice anyway. I have seen people get bitten by it over and over >>> again, and I have worked at companies where it is explicitly forbidden in >>> coding standards. >> >> In terms of application queries I generally agree. However, I think >> this rule does not apply to server side definitions, especially in >> regards to views and/or composite types. There are cases where you >> _want_ the view to be define as 'all fields of x'...In fact, it's >> pretty typical IMNSHO. It may be possible to expose this behavior. >> >> I'd like to see: >> select * from foo >> -- and -- >> select (foo).* >> exhibit different behaviors -- ().* is more a type operator, returning >> all the fields of foo, than a field list expression. This gives us a >> cool loophole to exploit for views that really want to be defined with >> *: >> create view particular_foos as select (foo).* from foo where something = true; >> create view something_complex as select (foo).*, (func(foo.field)).*; >> -- execute func() just one time please! >> >> The something_complex case above is a real problem in how it behaves >> currently -- sometimes without a hassle free workaround. Am I off my >> rocker? :-) I've made this point many times (prob got annoying a long >> time ago) but I'm curious if you guys agree... > > What you're suggesting makes sense to me. > > What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegantsolution than drop and re-create the view. Well, the workaround I was specifically talking about was dealing with the problem of composite type return from functions executing the function multiple times: select (func()).*; This gets expanded to select func().f1, func().f2, etc. This is the behavior I think has to go. if func returns foo and foo has 6 columns, func gets executed 6 times for each row. The workaround is this: select (q).f.* from (select func() as f) q; the problem here is that forcing the function call into a subquery can be awkward in non trival queries -- it causes other problems. What you are probably looking for is to be able to add columns to a view without recreating it: create table foo(...); create view v as select foo from foo; now you can just do: select (foo).* from v; small disclaimer: I don't actually do this much, it might cause other issues. postgres is pretty smart about detecting how composite type changes cascade to other structures. This is an exception! postgres=# create table foo(a int, b int, c int); CREATE TABLE postgres=# create view v as select foo from foo; CREATE VIEW postgres=# create view vv as select (v).foo.c; CREATE VIEW postgres=# insert into foo select 1,2,3; INSERT 0 1 postgres=# insert into foo select 2,4,6; INSERT 0 1 alter table foo drop column c; -- uh oh ALTER TABLE postgres=# select * from v; -- this seems ok foo -------(1,2)(2,4) postgres=# select * from vv; -- urk! postgres=# \d+ vv View "public.vv"Column | Type | Modifiers | Storage | Description --------+---------+-----------+---------+-------------c | integer | | plain | View definition:SELECT (v.foo)."........pg.dropped.3........" AS c FROM v; I don't actually mind this so much TBH...feature not bug. I hesitated fixing this because I was terrified someone might actually fix it. merlin