>Frankly, it seems like some sort of macro system is needed
Views, materialized views, CTEs.
Views, materialized views, CTEs.
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.
Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term).
> Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.
select min(category) as mincat, max(category) as maxcat,
min(value) as minval, -- etc
where ts between <start> and <end>
You can copy, paste and edit 100 times and create 100 view objects
Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it.
I think the usual SQL solution would be to implement a stored function and start smashing strings together
Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity.
Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)
1. To Union them into multiple views
2. To instantiate individual views per query, to reuse in other queries
3. To return to client directly
I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon
The min/max aggregations over a period of time per category is pretty straight forward (aggregating rows), but what do you mean by "along with the other columns linked to min/max"? How would other columns come into play after aggregating rows?
Like say you have a query that applies to one table and potentially others where the column names are slightly different. Why cant you just port that query?