Parameter passing and variables are client-side considerations. You haven't told us how you plan to execute the SQL.
IMO the most straight-forward API is a function. Whether you implement that function using a updating CTE or a sequence of separate SQL commands is up to you to decide and, if performance matters, benchmark.
Comparing a CTE and function in general doesn't really do much good. There are many non-performance concerns involved and the specific usage pattern involved will matter greatly in determining overhead.
Thanks David, that makes sense. My main front-end - at the moment - is LibreOffice Base. With Base, I can probably just create forms using the underlying tables. That said, I may also want to write a Python front-end, in which case I would prefer to let the database do the work it was designed to do.
