>Frankly, it seems like some sort of macro system is needed

Views, materialized views, CTEs.

No, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much.

Say I have a table with the following properties:

- It has a column that corresponds to an id

- It has a column that corresponds to a category

- It has a column that corresponds to a value

- It has a column that corresponds to a timestamp

- It has a bunch of other columns

I want to have a general transformation that allows me to:

Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.

emidln

Perhaps you're looking for a way of arranging SQL as an AST represented by data structures (or objects) that can be fed to a compiler. HoneySQL[0] is one such implementation of this idea and it makes your general transformation trivial for Clojure programs. You don't need to mess around with string concatenation because you have a predictable and extensible compiler for data structures (which are themselves easily composable/transformable/storable with Clojure) that you can trust to do the right thing. If you're using some weird database or need an esoteric syntax, extending the compiler to your clause is easy to do[1].

[0] https://github.com/seancorfield/honeysql

[1] https://github.com/seancorfield/honeysql#extensibility