4 ms·
Right, but can you 1) store CTEs and 2) pass arguments in to CTEs, such that the same basic 'code' can be shared between multiple different queries? I'm a prin
by gwd 4y ago
Right, but can you 1) store CTEs and 2) pass arguments in to CTEs, such that the same basic 'code' can be shared between multiple different queries?
I'm a principal engineer who is relatively new with SQL (just learned it 5 years ago, using it for a side project, not my daily work) and almost completely self-taught; and the biggest problem I have with SQL is that I find it nearly impossible to do even basic levels of DRY in a way that's not ugly.
- ttfkam 4y agoI believe what you're looking for is (in PostgreSQL) called a set returning function.
- sebazzz 4y agoThe problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions. Another issue is that the “SELECT” is also part of the function, which makes it even worse - so you can’t create just one function and reuse it all over the place. If only you could tell SQL that a certain JOIN always contains one row, or zero or one row (for a LEFT JOIN), then there would be more room for optimization.
- trimethylpurine 4y agoI think that's subjective. You can index to optimize for whatever your function is doing. I don't see any reason that the "select" limits reusability. If you define your function with relevant parameters you can create theoretically infinite reusability.
- trimethylpurine 4y agoYes, you can. Thats called a table valued function.