User-Defined Functions
A UDF computes and returns a value, callable inline inside a SQL statement (SELECT, WHERE, anywhere an expression is valid), and never performs DML. Two shapes matter: scalar UDFs return one value per input row; tabular UDFs (UDTFs) return a full table of rows and columns per input row.
CREATE FUNCTION apply_markup( price FLOAT, pct FLOAT) RETURNS FLOATAS$$ price * (1 + pct / 100) $$;SELECT order_id, apply_markup( base_ price, 15) AS final_price FROM orders;
| Stored procedure | Scalar UDF | |
|---|---|---|
| Invoked with | CALL | Inline inside a SQL expression |
| Can run DML/ | Yes | No |
| Return shape | Single value (often a status string) | One value per input row |
| Usable inside a SELECT list | No | Yes |
| Multiple calls per statement | One CALL per statement | Many, once per row |
UDFs support SQL, JavaScript, Python, Java and Scala. A SQL UDF body is a single expression, with no RETURN; a UDTF is called in the FROM clause, as TABLE(my_udtf(...)). SQL UDFs are the simplest for pure calculations; Python and Java open the door to external libraries and more complex logic, at the cost of higher invocation overhead per row than a native SQL expression.
STORED PROCEDURE
INPUTCALLarchive_old_orders('2025-01-01')
RUNSProcedure bodyINSERT … ; DELETE … ;DML and DDL allowed
OUTPUT'Archive complete'one value (or a table)
SCALAR UDF
3 rows
FUNCTIONapply_markup(price, 15)one call per rowno DML
3 values
TABULAR UDF (UDTF)
1 row
TABLE FUNCTIONsplit_to_items(…)FROM orders, TABLE(…)no DML
0..N rows

