Curious what your opinion on plpgsql functions is? Could easily solve your initial problem (if I follow). They don't seem to come up much though

I do like PL\pgSQL functions, and I think they can to some extent be used to solve this problem, though I think they are limited in how their internal structure is parameterizable.

I am rarely in a position at my client projects where I can employ PL\pgSQL though, so I opt more for out-of-database solutions for composing my queries, as that usually is easier to debug.