User-Defined Functions

A user-defined function (UDF) is a custom function, written by a database developer, that behaves like a built-in function such as UPPER() or ROUND() — it can be called directly inside a SELECT list, a WHERE clause, or anywhere else an expression is expected. Where a stored procedure is invoked as its own standalone statement with CALL, a function is invoked inline, as part of a larger query.

Scalar functions vs table-valued functions

Type	Returns	Used like
Scalar function	A single value (a number, string, date, etc.)	A built-in function inside SELECT, WHERE, or ORDER BY
Table-valued function	A full result set (a set of rows and columns)	A table or view inside a FROM clause

Worked example: a scalar function

Suppose a discount needs to be applied to a price wherever it appears across many queries. Rather than repeating the same arithmetic expression everywhere, wrap it in a function:

PostgreSQL: a scalar function

SQL

CREATE FUNCTION discounted_price(p_price NUMERIC, p_discount_pct NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_price * (1 - p_discount_pct / 100), 2);
END;
$$;

-- Used directly inside a SELECT, just like a built-in function
SELECT
  product_name,
  price,
  discounted_price(price, 15) AS sale_price
FROM products;

The function can also be used in a WHERE clause, exactly like any other expression:

Using the function to filter rows

SQL

SELECT product_name
FROM products
WHERE discounted_price(price, 15) < 50;

Worked example: a table-valued function

A table-valued function returns a whole result set and can be queried like a table. This is useful for encapsulating a parameterized query that is reused with different inputs:

PostgreSQL: a table-valued function

SQL

CREATE FUNCTION orders_above(p_amount NUMERIC)
RETURNS TABLE (order_id INT, customer_id INT, order_total NUMERIC)
LANGUAGE sql
AS $$
  SELECT order_id, customer_id, order_total
  FROM orders
  WHERE order_total > p_amount;
$$;

-- Queried like a table
SELECT * FROM orders_above(500)
ORDER BY order_total DESC;

Functions vs procedures

A function returns a value (scalar or table) and can be embedded inside a query expression.
A procedure is invoked as its own statement and does not have to return a value; it is meant for a sequence of actions.
A function generally should not modify data as a side effect, since it may be called anywhere in a query, potentially multiple times per row — a procedure is the appropriate place for INSERT/UPDATE/DELETE logic.

Syntax and capabilities vary by dialect

PostgreSQL's PL/pgSQL, SQL Server's T-SQL, Oracle's PL/SQL, and MySQL's function syntax all differ in keywords, parameter syntax, and what a function is permitted to do — for example, some dialects strictly forbid a scalar function from modifying data, while others are more permissive. Always check the target database's documentation for the exact CREATE FUNCTION syntax and restrictions before porting logic between systems.

A user-defined function extends SQL with custom, reusable logic callable like a built-in function.
Scalar functions return a single value; table-valued functions return a queryable result set.
Functions are used inline within a query expression, unlike procedures, which are called as standalone statements.
Dialect syntax and rules (especially around side effects) vary significantly, so check documentation for the target database.