Comment by closeparen
19 hours ago
The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.
19 hours ago
The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.
Even so, "FROM t SELECT t.foo, …" has an ergonomic advantage over "SELECT t.foo, … FROM t" in that editors can autocomplete column names without needing to backtrack while editing.
IIRC, this is why C# query syntax uses the former.
The problem with the query part is that query fragments aren't composable.
CTEs are how you compose SQL.
I don't quite like how the same CTE lives in 60 different places in my codebase, but at least the WITH clause changed things for me.
Also really liked Snowflake's result_scan for composing chains, mostly because I don't rerun expensive parts again and again. You can use ->> as a shortcut, but I don't think it uses results caching internally to skip waiting for them to all re-run & actually optimizes the whole thing.
you create one view and have the ctes query that, to deduplicate the implementation