Avoid use of `Signature::user_defined` for normal functions

_Originally posted by @alamb in https://github.com/apache/datafusion/issues/12698#issuecomment-2388306202_

> > Can we make it a goal that not to require Signature::user_defined to implement useful things?
>
> Yes, I think this should be the goal
>
> The usecase for `Signature::user_defined` that I recall was to implement coercion rules for very specific functions (like coalesce)

Extend signatures and function resolution so that `Signature::user_defined` is not needed.

Start by tracing Signature::user_defined through function signatures and function resolution, then review the discussion linked from this issue for the intended coercion behavior. Done means normal functions can be implemented and resolved without requiring Signature::user_defined, while preserving the special coercion behavior needed by functions such as coalesce.

Written by the indexing model from the issue text.