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.