Re: SELECT * in a CREATE VIEW statement doesn't update column set automatically

On May 6, 2010, at 4:29 PM, Merlin Moncure wrote:
> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(at)dunslane(dot)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 elegant solution than drop and re-create the view.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net