Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

On 10/30/2011 03:50 PM, Eric Ridge wrote:
> On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke<mark(at)mark(dot)mielke(dot)cc> wrote:
>> 2) Not deterministic (i.e. a database change might cause my code to break),
> Okay, I'm inventing a use-case here, but say you have a "users" table
> with various bits of metadata about the user, including password.
> Maybe, regardless of database changes, you never want the password
> column returned: SELECT * EXCLUDING (password) FROM tbl_users;
>
> Changes of omission can break your code just as easily.

I think I wasn't as clear as I intended. In many ways, I think use of
"*" in the first place is wrong for code (despite that I do it as well).
Therefore, "* EXCLUDING (...)" would also be wrong. It comes to "does
the code know what it wants?"

In the above case - maybe you don't want password - what about social
insurance number, credit card number, or any other private bit? The only
way to truly know you aren't accidentally pulling in fields you don't
need or want to unnecessarily expose on the wire - is to specifically
list the fields you DO want, which is precisely to not use "*" at all.

A particular area that I don't like "*" is that my code may make an
assumption about the exact field names, or the field order that comes
out. If this is explicitly specified, then it will survive "ALTER
TABLE", or a restore of the table with columns in a different order, or
a replacement of the table with a view. However, if I use "*", then my
code is likely to fail in any of these cases, and possibly fail in some
unexpected way. For example, in a language such as Perl with DBI
returning a hash, I may accidentally assume that the field is always
undef. It might even pass some designer testing if the value is usually
NULL = undef, and I fail to simulate the case where it is not.

"select *" is not deterministic from a programming perspective.

--
Mark Mielke<mark(at)mielke(dot)cc>