Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformUser-Defined Functions

User-Defined Functions

User-Defined Functions

Definition

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 FLOAT
  AS
  $$ price * (1 + pct / 100) $$;

SELECT order_id, apply_markup(base_price, 15) AS final_price
FROM orders;
Stored procedureScalar UDF
Invoked withCALLInline inside a SQL expression
Can run DML/DDLYesNo
Return shapeSingle value (often a status string)One value per input row
Usable inside a SELECT listNoYes
Multiple calls per statementOne CALL per statementMany, 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.

Three shapes of server-side code, with illustrative values. A stored procedure is started with CALL, may run DML such as INSERT and DELETE, and returns one value. A scalar UDF runs inside a SELECT and turns each input row into exactly one value: three prices in, three marked-up prices out. A tabular UDF, called through TABLE(...), turns one input row into zero or more rows: one order in, two item rows out.STORED PROCEDURESCALAR UDFTABULAR UDF (UDTF)INPUTCALLarchive_old_orders('2025-01-01')RUNSProcedure bodyINSERT … ; DELETE … ;DML and DDL allowedOUTPUT'Archive complete'one value (or a table)base_price40.0095.5012.00FUNCTIONapply_markup(price, 15)one call per rowno DMLfinal_price46.00109.8313.80one order rowORD-8842 · order_jsonTABLE FUNCTIONsplit_to_items(…)FROM orders, TABLE(…)no DMLsku · qtySNW-001 · 2SNW-207 · 13 rows3 values1 row0..N rows

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

base_price40.0095.5012.00

3 rows

FUNCTIONapply_markup(price, 15)one call per rowno DML

3 values

final_price46.00109.8313.80

TABULAR UDF (UDTF)

one order rowORD-8842 · order_json

1 row

TABLE FUNCTIONsplit_to_items(…)FROM orders, TABLE(…)no DML

0..N rows

sku · qtySNW-001 · 2SNW-207 · 1
Figure 6.9 Count the rows going in and coming out. A procedure is a CALL that can change data and returns one value; a scalar UDF returns one value per row; a UDTF can return several rows per row. Neither kind of UDF may run DML. Values are illustrative (15% markup).