User-Defined Functions

A user-defined function (UDF) is a named routine that takes parameters and returns a value (scalar function) or a table (table-valued function).

Concept

A user-defined function (UDF) is a named routine that takes parameters and returns a value (scalar function) or a table (table-valued function). Unlike procedures, functions are designed to be used inside SQL expressions: in SELECT lists, WHERE clauses, and FROM clauses.

Why It Matters

UDFs encapsulate reusable calculations (tax, age, formatted codes, business classifications) so they are defined once and used consistently in many queries.

Syntax

-- PostgreSQL scalar function (SQL language)
CREATE OR REPLACE FUNCTION fn_name(p1 type, ...)
RETURNS return_type
LANGUAGE sql
IMMUTABLE        -- or STABLE / VOLATILE
AS $$
    SELECT ...;
$$;

-- MySQL
DELIMITER //
CREATE FUNCTION fn_name(p1 type) RETURNS return_type
DETERMINISTIC
BEGIN
    RETURN expression;
END //
DELIMITER ;

-- SQL Server
CREATE FUNCTION dbo.fn_name(@p1 type) RETURNS return_type
AS
BEGIN
    RETURN expression;
END;

-- Oracle
CREATE OR REPLACE FUNCTION fn_name(p1 IN type) RETURN return_type IS
BEGIN
    RETURN expression;
END;
/

Example

26.1 Scalar functions Price including GST:

Function with procedural logic (PL/pgSQL):

26.2 Table-valued functions Return a set of rows, used in FROM like a table.

Result of SELECT * FROM orders_for_customer(2);:

MySQL does not support table-valued functions (use views, procedures, or derived tables). Oracle supports pipelined table functions and SQL macros (21c+).

26.3 Determinism and volatility Deterministic / IMMUTABLE Same inputs → same output, no database access price_with_gst(100, 18)

Same result within a single statement; reads tables Lookup a tax rate from a table VOLATILE / non-deterministic Result may change on every call

Functions using random(), now() (STABLE in PostgreSQL), or modifying data

Marking correctly allows optimizations (constant folding, use in expression indexes, caching). MySQL requires DETERMINISTIC, NO SQL, or READS SQL DATA declarations when binary logging is enabled.

26.4 Stored procedure vs function Zero or more result sets, OUT parameters Exactly one value or one table CALL / EXEC Inside SQL expressions (SELECT fn(x)) Use in SELECT/WHERE Modify data (DML)

Generally no in SQL Server (side effects prohibited); allowed in PostgreSQL/Oracle functions but not recommended when called from queries Transaction control (COMMIT/ROLLBACK)

Yes (PostgreSQL procedures, MySQL, SQL Server, Oracle) Business operations, batch jobs

Reusable calculations, parameterized views Full TRY/CATCH or EXCEPTION blocks

Limited in SQL Server (no TRY/CATCH in functions) Performance warning: scalar UDFs called for every row can be slow (SQL Server executed them row-by-row until scalar UDF inlining in SQL Server 2019; a UDF that runs a query per row is effectively a hidden correlated subquery). Prefer inline SQL, inline table-valued functions, or joins for large data.

-- PostgreSQL
CREATE OR REPLACE FUNCTION price_with_gst(p_price NUMERIC, p_rate NUMERIC DEFAULT 18)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ROUND(p_price * (1 + p_rate / 100), 2);
$$;

-- MySQL
DELIMITER //
CREATE FUNCTION price_with_gst(p_price DECIMAL(12,2), p_rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_price * (1 + p_rate / 100), 2);
END //
DELIMITER ;

-- SQL Server
CREATE FUNCTION dbo.price_with_gst(@price DECIMAL(12,2), @rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
AS
BEGIN
    RETURN ROUND(@price * (1 + @rate / 100), 2);
END;

-- Usage
SELECT product_name, price, price_with_gst(price, 18) AS gross_price FROM products;
-- SQL Server requires the schema prefix: dbo.price_with_gst(price, 18)

CREATE OR REPLACE FUNCTION salary_band(p_salary NUMERIC)
RETURNS VARCHAR
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF p_salary >= 150000 THEN
        RETURN 'A';
    ELSIF p_salary >= 90000 THEN
        RETURN 'B';
    ELSE
        RETURN 'C';
    END IF;
END;
$$;

SELECT first_name, salary_band(salary) FROM employees;

-- PostgreSQL: RETURNS TABLE
CREATE OR REPLACE FUNCTION orders_for_customer(p_customer_id INT)
RETURNS TABLE (order_id INT, order_date DATE, total_amount NUMERIC)
LANGUAGE sql
STABLE
AS $$
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id;
$$;

SELECT * FROM orders_for_customer(2);

-- SQL Server: inline table-valued function (preferred — optimized like a parameterized view)
CREATE FUNCTION dbo.orders_for_customer(@customer_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = @customer_id
);

SELECT * FROM dbo.orders_for_customer(2);

-- Using a TVF per row: CROSS APPLY (SQL Server, Oracle 12c+) / LATERAL (PostgreSQL, MySQL 8.0.14+)
SELECT c.name, t.order_id, t.total_amount
FROM customers c
CROSS APPLY dbo.orders_for_customer(c.customer_id) t;          -- SQL Server

SELECT c.name, t.order_id, t.total_amount
FROM customers c
CROSS JOIN LATERAL orders_for_customer(c.customer_id) t;       -- PostgreSQL

Expected Result

SELECT first_name, salary, salary_band(salary) AS band
FROM employees
WHERE dept_id = 2;

Common Mistakes

Using scalar UDFs with embedded queries in large SELECTs (N hidden queries).

Wrong volatility/determinism declaration, causing wrong cached results or blocking optimizations.

Using a UDF on an indexed column in WHERE, making the predicate non-sargable.

Trying to modify data in a SQL Server function.

Forgetting the dbo. prefix for SQL Server scalar functions.

Best Practices

Keep functions small and pure.

Prefer inline table-valued functions over multi-statement ones (SQL Server).

Declare volatility accurately.

Test functions with NULL inputs and edge cases.

Interview Relevance

Medium: "Procedure vs function?", "Scalar vs table-valued function?", "Can a function modify data?", "Why can UDFs hurt performance?".

Practice Questions

Write a function returning a customer's full lifetime value (non-cancelled).

Write a table-valued function returning products in a given category above a price.

Explain why WHERE my_fn(order_date) = 2026 may be slow.

Convert salary_band into a CASE expression inline and compare.

Write a function that returns the number of working days between two dates (weekdays only).