SELECT * in a CREATE VIEW statement doesn't update column set automatically
in [PgSql]
|
Prev: [HACKERS] errcontext from PL/pgSQL (was message style for errcontext)
Next: New buildfarm client release
From: Jim Nasby on 17 May 2010 14:15 On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: > On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(a)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 elegant solution than drop and re-create the view. -- Jim C. Nasby, Database Architect jim(a)nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |