On 10/30/2011 03:50 PM, Eric Ridge wrote:
On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke<m...@mark.mielke.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<m...@mielke.cc>

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to