How to write the `apply` function in postgresql
I'd really like to be able to write sql functions with apply ...

Short answer: You can't.
Not with PostgreSQL.

... to have a function resembling apply in other languages.

Never come across &quot;apply&quot;, myself nor do I think it &quot;adds&quot; much to the code.
Given that you'll spend far more time reading [other people's] SQL than writing it, which of these is clearer / easier to understand?
return apply( greatest, arr );
return greatest( arr );

Now, I could see the attraction if you were writing your code in 'C' and so could use Function Pointers for that first argument, which can be immensely powerful, but in a SQL context?  I can't really see the benefit.