Thanks for writing what you have. These all sound like reasonable approaches. Just to add some extra options/thoughts because I’ve been thinking about this la

Thanks for writing what you have. These all sound like reasonable approaches. Just to add some extra options/thoughts because I’ve been thinking about this lately, for cases where CASE doesn’t work well on order by, you can use dynamic queries to construct the final query in SQL. Another option might be to include a splash of templating to your .sql files, and parse them at run time, or use them to generate the final .sql files beforehand so that they can be tested directly via something like pgtap (repeat yourself via templates instead of by hand). The main reason I like the sound of including the queries in separate .sql files is precisely so that they can be easily and directly included in tests. The downside is the query isn’t included next to the code that uses it, so a little hunting needs to be done to find it (always tradeoffs!).