I use select * a lot in PostgreSQL. I do this any time I have a relatively self-contained table which can't be further normalized very far, and CRUD queries wrapped behind a stored procedure which returns the table type.
The reason I use select * there is that it reduces a maintenance point when it comes to return types and tables. In that case, I am guaranteed to get a return type which matches the table type. If the two are closely linked, then select * makes a lot of sense, and the alternatives aren't going to do better.
This makes schema changes more compatible than trying to change the column everywhere in every query in addition to the higher levels of code.
I would generally agree that an application calling select * from table is a bad thing for the same reasons, but if you are encapsulating your db behind an API, a lot of reasons shift.
I have the opposite preference. If there are a big bag of breakages when I change the schema, at least I see where they are. I'm not a fan of relying solely on tests when the DB itself can notice introduced discrepancies.
The fundamental question is where and what you are doing.
As I say, I use select * a lot, but these are in two groups:
1. In UDF's to ensure the proper return type, and
2. Against UDF's where it is the UDF designer's job to maintain the software interface contract.
In those cases, select * works, but it only does so because you have dependency injection and can decide on a case-by-case basis whether to pass the changes up, wrap them in a view, or the like. Most of the time this is a simple choice. The app needs a new column that we are storing and so.... But in a multi-app environment there may be other choices, and that dependency injection makes all the difference in the world.
EDIT: for example, if you have a UDF returning the type of a given table, then you may want to use select * because you have already decided these are to be tied together. Similarly select * from my_udf() is not a bad thing the way that select * from my_table is.
The reason I use select * there is that it reduces a maintenance point when it comes to return types and tables. In that case, I am guaranteed to get a return type which matches the table type. If the two are closely linked, then select * makes a lot of sense, and the alternatives aren't going to do better.
This makes schema changes more compatible than trying to change the column everywhere in every query in addition to the higher levels of code.
I would generally agree that an application calling select * from table is a bad thing for the same reasons, but if you are encapsulating your db behind an API, a lot of reasons shift.