UDF or User Defined Functions are read-only functions in SAP Hana. They are not aligned with DDL and DML operations. It is one of the two logical containers used in SQLScript, the other being Procedures.
UDFs are further categorized as Scalar and Table UDFs. These are distinguished by three qualities –
- How they are consumed in SQL SCript
- What are the input and output parameters
- What kind of functions are allowed in them.
Scalar UDF –
- Accept Primitive SQL Type as input and return Scalar values as output.
- Scalar UDF allow only expression, no SQL query or function.
- Called in SELECT and WHERE clauses.
Table UDF –
- Along with Primitive SQL Type, accept Table Type as input and return a table as output.
- Allows expressions and other read-only functions.
- Called in FROM clause.