(Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
My rule is to refactor into a view when and only when it's going to be reused.
> Postgres optimizes across views, but not across CTEs
Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope.
I've read quite a few people on the email lists claiming that this is a benefit of CTEs. How they justify that, I don't know. Sure, you get predictability of performance, but I have never once written a query worried about how predictably it performed, instead of how fast it performed.
1. Postgres will never implement query hints, because query hints are evil
2. In the real world people need them anyway, so they (ab)use implementation details that offer some control over the query plan (WITH is one example, see also OFFSET 0 and other hacks)
3. Everyone starts relying on these implementation details, e.g. you'll find people actually recommending this as a way to optimize queries in the mailing lists
4. Now when someone asks to fix the planner, someone else will point out this will break all the queries that have been "optimized" by depending on the old behavior
Postgres is generally a fairly sane project though, so I have some confidence they will come to their senses. Eventually.
I'm not sure what you mean by "opaque"; they encapsulate logic, which is a plus.
On the "single namespace" thing, that sort of depends on the RDBMS; e.g., in postgres, within a single DB, one can have multiple schemas, and there is nothing stopping a view in one schema from referencing objects in another schema, so views for one database may exist in separate schemas, which act as namespaces. Other DBs offer equivalent functionality, though the names of structures may be different.
Notably, MSSQL does optimize across CTEs, so there's not as much pressure to break out to views on that platform. And if you're feeling especially perverse, you can make a CTE a view, which sorta... sporks the problem.