What Are User-Defined Functions (UDFs) in SQL? Definition and Examples

User-defined functions in SQL are custom functions that developers create to encapsulate reusable logic directly inside the database. Unlike built-in functions such as SUM() or AVG(), UDFs let you define the logic, inputs, and return type. This article covers the definition, types, syntax, and how UDFs behave in distributed SQL environments like YugabyteDB.

What Is a User-Defined Function in SQL?

A UDF is a named, reusable block of logic stored in the database and invoked like any other function. You define it using the CREATE FUNCTION statement, give it input parameters, declare a return type, and write the function body in SQL or a procedural language like PL/pgSQL. Once created, it can be called from any SELECT, WHERE, or FROM clause where an expression is valid.

The four required components of any UDF are the function name, input parameters with their data types, a RETURNS clause specifying the output type, and the function body containing the logic.

How Are UDFs Different From Stored Procedures?

The key distinction is in what they return and where they can be used. A UDF returns a value and can be embedded directly inside a SQL expression. A stored procedure performs an action and does not return a value in the same way, and it cannot be used inside a SELECT statement. If you need to compute something and pass the result downstream in a query, a UDF is the right tool.

What Are the Main Types of UDFs in SQL?

SQL databases support two primary UDF types: scalar functions, which return a single value, and table-valued functions, which return a result set. Choosing the right type depends on the shape of the data your query needs back.

What Is a Scalar Function?

A scalar function takes one or more inputs and returns exactly one value. Common use cases include calculating a discounted price, formatting a string, or converting units. Here is a basic PL/pgSQL example:

CREATE OR REPLACE FUNCTION apply_discount(price NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql AS $$

BEGIN
    RETURN price - (price * rate);

END;
$$;

You call it inside a SELECT like any built-in function:

SELECT product_name, apply_discount(list_price, 0.15) AS
discounted_price
FROM products;

What Is a Table-Valued Function?

A table-valued function returns a result set rather than a single value. This makes it useful for encapsulating filtered or transformed datasets that you want to treat like a table in a query.

Table-valued functions come in two forms. Inline table-valued functions consist of a single RETURNS TABLE query and perform better because the optimizer can treat the function body like a subquery. Multi-statement variants build the result set across multiple steps.

CREATE OR REPLACE FUNCTION active_customers(min_orders INT)
RETURNS TABLE(customer_id INT, customer_name TEXT)
LANGUAGE sql AS $$

    SELECT id, name FROM customers WHERE order_count >= min_orders;

$$;

This function can then be queried directly:

SELECT * FROM active_customers(5);

How Do You Create a User-Defined Function in SQL?

The CREATE FUNCTION statement is the standard entry point. A complete function definition requires the function name, parameter list with types, the RETURNS clause, a LANGUAGE declaration, and the function body.

Here is a realistic working example that calculates a tax-inclusive total:

CREATE OR REPLACE FUNCTION total_with_tax(subtotal NUMERIC, tax_rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql AS $$

BEGIN
    RETURN subtotal + (subtotal * tax_rate);

END;
$$;

Call it in a query:

SELECT order_id, total_with_tax(order_subtotal, 0.08) AS total_due
FROM orders;

The LANGUAGE clause tells the database which procedural extension to use: sql works for simple, single-expression functions, and plpgsql is the right choice when you need conditional logic, loops, or exception handling.

How Do You Update or Remove a UDF?

To modify an existing function without dropping it first, use CREATE OR REPLACE FUNCTION with the same function name and signature. This overwrites the function body and attributes while preserving any permissions granted on it. To remove a function entirely, use DROP FUNCTION function_name(parameter_types). Including the parameter types is important when multiple overloads of the same function name exist.

When Should You Use UDFs in a SQL Database?

UDFs are well-suited to three situations: when the same business logic must produce consistent results across multiple queries, when a complex calculation would otherwise bloat query code with repeated subexpressions, and when you want to expose a clean, reusable interface across application layers.

That said, scalar UDFs carry a performance trade-off. Because they evaluate row by row rather than as a set-based operation, they can add overhead on large datasets. Inline table-valued functions avoid this because the optimizer can inspect their body directly. For high-volume queries, prefer inline table-valued functions or consider whether the logic can be expressed as a view or common table expression.

How Do UDFs Work in YugabyteDB?

YugabyteDB’s YSQL layer reuses PostgreSQL’s own query processing code, including its PL/pgSQL implementation, rather than reimplementing SQL support from scratch. That’s why UDFs written in SQL or PL/pgSQL run in YSQL with the same syntax and semantics as PostgreSQL, in most cases without changes.

The CREATE FUNCTION statement in YSQL follows the same syntax described throughout this article, and YSQL supports both scalar and table-valued user-defined functions. One scoping note: YSQL currently supports language sql and language plpgsql only. PL/Python, PL/Perl, PL/Tcl, and C-language functions, which PostgreSQL supports, aren’t available in YSQL, so UDFs written in those languages need to be ported rather than migrated as-is.

This compatibility is particularly relevant for teams migrating from PostgreSQL or other relational databases. SQL and PL/pgSQL logic generally transfers with minimal changes, and the database gains distributed scalability and resilience in the process. As with any migration, it’s worth testing procedures that rely on dynamic DDL or explicit transaction control, since a small number of PostgreSQL behaviors in those areas differ in a distributed environment.

Learn more about YSQL subprogram support and explore how YugabyteDB handles PostgreSQL-compatible UDFs at scale. Schedule a demo today.