This is something I've never thought about before, and haven't had a use case for, so I'm genuinely interested in learning a little more about your use cases if you can elaborate a little futher.

Sometimes you want to be able to do something like "run some SQL, but instead of using the normal tables use these temp tables I just created."

In particular, I wanted to do this in SQLite recently. I wanted to have one write process which would always remain unblocked. And I also wanted to be able to run certain tasks which would do some temporary/discardable DB manipulations as part of producing an output file. These tasks could open the SQLite DB in read-only mode; load relevant data into temp tables, manipulate that data, and write the output file. Everything would have worked great if only SQL were a more composable language.