SQL User Defined Function (UDF) ​

A UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Yellowbrick supports creating SQL user defined functions from version 5.4. Users can create custom UDF using SQL statements. SQL UDFs are meant to provide a handy mechanism to roll up expressions into a function-name and are expanded inline by the front-end database during parsing or planning.

Things to know before creating SQL UDFs: ​

1. SQL UDFs cannot be created in the pg_catalog, sys, and yb_stat schema.
2. Yellowbrick Data Warehouse SQL functions can only be "scalar functions". They operate on a single row and return exactly one value that is a base database data type. They cannot return a result set or record.
3. CREATE FUNCTION does not support TRANSFORM, WINDOW, LEAKPROOF, SET, COST, and ROWS clauses.
4. Function definition requirements:

• Single table-free select statement: The SELECT statement contained within the function must not include a FROM clause.

• Single Target Expression: The SELECT statement within the function must return exactly one column from the current row only.

• Non-Recursive: SQL functions cannot call themselves.

• Non-null and Non-empty: The function must perform an operation and cannot be a no operation.

5. SQL UDFs cannot be referenced in a cross-database query.

   Example:

   sql

   SELECT one, one AS two, yellowbrick.public.add_numbers( one, two ) FROM sys.const;
   
   ERROR: cross-database references are not allowed: yellowbrick.public.add_numbers

6. CREATE FUNCTION VOLATILITY constraints:

• If the function is defined to be Immutable, then the function definition can only contain immutable functions.
• If the function is defined to be Stable, then the function definition cannot contain volatile functions.
• If the function is defined to be Strict, then the function can only contain strict functions and cannot have unused parameters.
• If the function uses volatile built-in functions it must be volatile (which is the default).