Hey Shmagi,
1) UDFs are more flexible than functions written in SQL, and give you much more fine grained control of the data. They can potentially be much faster than functions written in SQL, although that depends on what exactly you are trying to do and how exactly you have implemented it.
2) The current release version of MonetDB supports UDFs in the languages C and R. You can write UDFs in Python if you compile an in-development version of MonetDB as detailed
on the MonetDB blog, here.
3) If you want to create a UDF written in C you do not need to recompile MonetDB entirely, but you do need to compile your UDF and add the compiled library and the .mal and .sql files that detail how to link to your function to the MonetDB library directory. The documentation for how to that exactly can be found
here. It is an in-depth process that requires you to understand how MonetDB works internally. It might be easier to write an R UDF, or to compile the in-development version of MonetDB and write a Python UDF, as these do not require you to compile or link anything.
I hope that answers your questions. If you have any more let me know.