Before answering your questions, let's review:
Why is select * bad?
- You're (probably) selecting more columns than you need to. This means transferring more data over the network and less likelihood of using index-only scans or other sub-optimal execution plans.
- If your code uses select * to return data into a fixed list of variables, adding or removing any columns will cause this to break. So every time you change a table's columns you need to update these statements. If you don't need all the columns to start with or new columns you add, this increases your work for no benefit.
Now in your query:
with info as (
select id description_id,
prename,
name,
some,
other,
information
from person_description
)
select j.id, j.title, i.*
from jedi j
inner join info i
on j.fk_description = i.description_id
union all
select s.id, s.title, i.*
from sith s
inner join info i
on s.fk_description = i.description_id;
Presumably, you've selected exactly what you want from person_description. And, when you change this, you're editing the query anyway so can review if select * is still what you need. So yes, you could argue that in this case, it's OK. Make sure you're clear about the reasons why you do this!
A1. Everyone has their own pet preferences on what makes SQL "more readable". So all I'll say is:
Be consistent.
Make sure you stick with a formatting style throughout your application.
A2. When you create a view, the database needs to update the data dictionary to store information about it. This includes the columns, their data type and so on.
If you leave a view as select * then when you run many alter table commands you have to rebuild the view too. If this is a widely-used core view, this could trigger a rebuild lots of the data dictionary! By making the columns explicit you bound the limits of what's directly affected.
A3. Any difference between source code and database code becomes something you need to check. And remember that yes, in this case, the fact they don't match is acceptable. The more of these exceptions you create, the more thoroughly you need to document your decisions. And the harder it is to spot genuine differences. It also makes it tougher for new developers to join the project.
And, if for some reason the tables in your dev/test/prod databases have different columns, with select * your view will compile fine. But if a named column is missing you'll get an exception immediately. If this breaks a release, it makes root cause analysis easier!
So in my opinion, you need to have significant benefits to using select * instead of named columns in your views. Personally I think the benefit here is small at best. But see A1. ;)