3 ms·
What do you think about using SQL views and functions to make composable, modular SQL code?
by rzmmm 4y ago
What do you think about using SQL views and functions to make composable, modular SQL code?
- tbrownaw 4y agoIt doesn't work very well. Table Out1 has translated versions of fields AA..AZ from table In1. Table Out2 has translated versions of fields BA..BZ from table In1. table Out3 has translated versions of fields X..Z from table In2, and the translation depends on some of the AA..BZ fields, in both translated and untranslated forms. We'd end up with views that depended on other views and after expanding had like a dozen self-joins to add more fields that weren't included in the base view. And views that were selecting a big list of columns unchanged, plus adding a couple. But somehow despite being an absolute ^%#@!^&$! it still worked better than the pointy-clicky ETL tooling we'd been using previously. Mostly because of the run->debug->modify cycle time being faster, and only a little because of avoiding pointy-clicky stuff.
- vkazanov 4y ago1. Views are fine. They somewhat help with abstracting things away here and there, yes. But these are just macros, right? 2. What SQL functions are you talking about? There are builtins, e.g. avg, sum, and then there are User Defined Functions, which have nothing to do with SQL, mostly just a random external language or one of the non-portable PL/SQL flavours. Practically speaking, one cannot really write portable SQL outside of a very limited intersection of various dialects. And within this limit users just cannot build a reasonably portable code library. There is a reason why analysts working with SQL just copy massive query templates again and again and again...