USER-DEFINED FUNCTIONSProgrammability
Buy Me A Coffee

Named, reusable routines that accept parameters, compute a value, and return a scalar or table result.

Database-specific procedural extensions: PL/pgSQL in PostgreSQL, T-SQL in SQL Server, PL/SQL in Oracle. Allow complex logic, loops, and conditionals in the database. Powerful but can become a maintenance burden and are hard to test.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 Scalar functions only (no table-valued UDFs in native SQL). DETERMINISTIC / NO SQL / READS SQL DATA characteristics affect optimizer behavior.
PostgreSQL ✓ Supported 6.5 Supports scalar and set-returning (RETURNS TABLE / RETURNS SETOF) functions. LANGUAGE can be sql, plpgsql, python, c, and more.
SQL Server ✓ Supported 2000 Three types: scalar, inline table-valued (ITVF), and multi-statement table-valued (MSTVF). ITVFs are strongly preferred over MSTVFs for performance.
Oracle ✓ Supported 7 Functions can use PIPELINED RETURN to stream table results row by row. DETERMINISTIC hint improves optimizer reuse.
SQLite ✓ Supported 3.0.0 UDFs must be registered at the connection level via the application API (e.g., sqlite3_create_function in C, or conn.create_function() in Python). No CREATE FUNCTION DDL.

Details

UDFs are supported everywhere but vary widely in capability and performance characteristics. SQL Server scalar UDFs are infamous for row-by-row execution that defeats parallelism — inline table-valued functions (single SELECT, no BEGIN/END) are the idiomatic replacement for scalar logic that operates on sets. PostgreSQL's function overloading (same name, different argument types) is unique among the five engines. Oracle PIPELINED functions allow lazy evaluation of large result sets. MySQL's lack of table-valued UDFs means pivoting or multi-row generation requires stored procedures or workarounds. SQLite's approach — registering functions in the host language rather than via DDL — is architecturally different and means UDFs don't persist across connections unless re-registered.

Standard Syntax

CREATE FUNCTION fn_name(param type) RETURNS return_type AS -- body ;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 6.5 SQL Server: Since 2000 Oracle: Since 7 SQLite: Since 3.0.0

Per-Database Syntax & Notes

MySQL

Scalar functions only (no table-valued UDFs in native SQL). DETERMINISTIC / NO SQL / READS SQL DATA characteristics affect optimizer behavior.

CREATE FUNCTION add_tax(price DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC RETURN price * 1.1;

PostgreSQL

Supports scalar and set-returning (RETURNS TABLE / RETURNS SETOF) functions. LANGUAGE can be sql, plpgsql, python, c, and more.

CREATE FUNCTION add_tax(price NUMERIC) RETURNS NUMERIC LANGUAGE sql IMMUTABLE AS $$ SELECT price * 1.1; $$;

SQL Server

Three types: scalar, inline table-valued (ITVF), and multi-statement table-valued (MSTVF). ITVFs are strongly preferred over MSTVFs for performance.

CREATE FUNCTION dbo.add_tax(@price DECIMAL(10,2)) RETURNS DECIMAL(10,2) AS BEGIN RETURN @price * 1.1; END;

Oracle

Functions can use PIPELINED RETURN to stream table results row by row. DETERMINISTIC hint improves optimizer reuse.

CREATE FUNCTION add_tax(price NUMBER) RETURN NUMBER DETERMINISTIC AS BEGIN RETURN price * 1.1; END;

SQLite

UDFs must be registered at the connection level via the application API (e.g., sqlite3_create_function in C, or conn.create_function() in Python). No CREATE FUNCTION DDL.

-- Python example conn.create_function('add_tax', 1, lambda p: p * 1.1) -- Then use in SQL: SELECT add_tax(price) FROM products;