Is your feature request related to a problem or challenge? Please describe what you are trying to do.

A classic part of query optimization is algebriac transformations such as partially evaluating expressions once at plan time rather than over and over for each row during execution time. Part of such frameworks is knowing when functions can be rewritten -- so that the optimizer knows that sqrt(4) can be safely rewritten to 2 but that it can't rewrite functions like rand() < 0.5

Postgres uses the notion of Volitility to express this notion https://www.postgresql.org/docs/current/xfunc-volatility.html

Volitile: (e.g. rand())
Stable: (e.g. now())
Immutable: (e.g. sqrt)

Describe the solution you'd like

Design and add an annotation on all Functions (user defined and built in) that express their volatility category:
Volatile: (e.g. rand())
Stable: (e.g. now())
Immutable: (e.g. sqrt)

Describe alternatives you've considered
Design TBD

Additional context
This is foundational for PRs such as #1066. See specifically some comments from @jorgecarleitao such as #1066 (comment)