Yeah, I think composability is one of the biggest things missing from SQL.
The issue is that composability is often tied to actually moving data around in the database which has terrible performance. That is, you can compose a query of multiple queries that dump partial data sets into temp tables.
Views get you part of the way there, but they are designed to be long lived and are visible to all database users until they are dropped. This means its dangerous to change them or clean them up, as its not always clear who they are being used by.
Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me.
I completely agree that this is one of the major lackings of SQL.
Most databases offer the WITH syntax, but, bizarrely, the SQL standard specifies that the WITH block should materialised separately, which prevents it from being used as a device for abstraction.
Oracle is the only database I'm aware of that allows the WITH block, but doesn't impose the optimiser fence.
"Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me."
You mean like temp tables in MSSQL? They come in various flavours, they're all pretty handy. The most flexible of these is the XML variable, but there are far simpler options than that.
The issue is that composability is often tied to actually moving data around in the database which has terrible performance. That is, you can compose a query of multiple queries that dump partial data sets into temp tables.
Views get you part of the way there, but they are designed to be long lived and are visible to all database users until they are dropped. This means its dangerous to change them or clean them up, as its not always clear who they are being used by.
Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me.