But views and functions still require you to persist those objects in the database first. There's no such thing as a query "variable" that you can then re-use in multiple subsequent statements. Of course, you can use table variables or temporary tables to hold intermediate data, but those are eagerly evaluated, whereas functions, views and CTE's are lazily evaluated, and that allows for a massive performance boost (due to optimization).

I can see the appeal of such a construct in SQL. The requirement that reusable objects are persisted in the database requires a top-down design approach, and that doesn't really blend well with modern coding practices.

There is no limit to reuse of views?? You can create as many intermediate or base views as you like, within a view definition, CTE’s have local scope and are similar to variables, if you need schema scope, just define the query block as a view instead. UDFs are simple to create are lazy and can be easily reused and composed, in Postgres they are first class objects, scalar UDFs can be called anywhere in SELECT and Set Returning UDFs in the WHERE clause, use more than 1 and they also automatically Lateral Joined - which is incredibly powerful and composable - when you get your head around them

No, CTEs do not have local scope, at least not like variables. CTEs are statement-scoped; no matter how many SQL statements you have in your current scope, your CTE vanishes after the first statement terminates.