SQL 82 🛢️ User-Defined Functions (Scalar and Table-Valued) - KandZ

⏱️ Estimated read: 21 min | |

A stored procedure is a program the database runs on demand. A user-defined function is a value the query calls, either in a column expression or in a FROM clause. The two look similar, but they are used differently, optimized differently, and limited differently. A scalar function returns a single value. A table-valued function returns a set of rows. Both are stored in the catalog and called by name, and both are the mechanism that lets a query express logic that the built-in functions cannot. This chapter covers the two forms, the differences between them, and the places where each is appropriate.

Key point: A user-defined function runs in the context of the calling query. Unlike a stored procedure, a function cannot modify the database state in most databases. A function that inserts, updates, or deletes rows is either prohibited or an anti-pattern. A function is for computation, not for side effects.

Why user-defined functions exist

The repetition problem. The same expression appears in many queries. A formula for the tax, a rule for the discount, a computation for the distance. Copying the expression into every query is error-prone and hard to maintain. A scalar function captures the expression once.

The composition problem. A query needs a derived value that the built-in functions cannot express. The CASE expression is one way, but a function can encapsulate the logic with a name and a set of parameters.

The row-set problem. A query needs to expand a value into multiple rows. A string that contains a list, an array, a JSON document. A table-valued function takes the value and returns a set of rows that the query can join against.

The parameterization problem. A query needs a parameter that affects the result, not the row count. A function takes the parameter and returns the value, and the query uses the value in the SELECT list or the WHERE clause.

The portability problem. The built-in functions differ between databases. A user-defined function wraps the database-specific behavior behind a name, and the queries call the name.

The testing problem. A scalar function is a pure computation, and it can be tested in isolation. The test cases are the inputs and the expected outputs, and the function is the unit under test.

The schema documentation problem. A function is a named, documented, reusable piece of logic. The catalog shows the signature, the body, and the dependencies. The schema is easier to understand.

a. Scalar functions

A scalar function takes zero or more parameters and returns a single value. It is used in a column expression, a WHERE clause, or any place a value is expected.

The PostgreSQL syntax

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT amount * 0.19;
$$;

The LANGUAGE sql function body is a single SQL expression. The $$ ... $$ is the dollar-quoted string that delimits the body.

SELECT id, calculate_tax(total) AS tax
FROM orders;

The function is called in the SELECT list, and the result is a computed column.

The SQL Server syntax

CREATE FUNCTION dbo.CalculateTax(@amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @amount * 0.19;
END;

The RETURNS clause declares the return type, and the RETURN statement returns the value. The dbo. prefix is the schema name, which is required in SQL Server.

SELECT id, dbo.CalculateTax(total) AS tax
FROM orders;

The MySQL syntax

CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN amount * 0.19;

The DETERMINISTIC keyword tells MySQL that the function returns the same result for the same input, which allows the optimizer to cache the result. The RETURN is a single expression.

SELECT id, calculate_tax(total) AS tax
FROM orders;

The function with a CASE expression

A scalar function can use a CASE expression to implement a decision.

CREATE OR REPLACE FUNCTION order_status_label(status VARCHAR)
RETURNS VARCHAR
LANGUAGE sql
AS $$
    SELECT CASE status
        WHEN 'pending' THEN 'Pending review'
        WHEN 'shipped' THEN 'In transit'
        WHEN 'delivered' THEN 'Complete'
        ELSE 'Unknown'
    END;
$$;

The function takes the status and returns the label. The CASE expression is the body.

The function with a subquery

A scalar function can contain a subquery that reads from a table.

CREATE OR REPLACE FUNCTION order_total(order_id BIGINT)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(quantity * price), 0)
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = order_id;
$$;

The function reads from the order_items and products tables and returns the sum. The function is called in the SELECT list.

The scalar function in a WHERE clause

SELECT id, order_date
FROM orders
WHERE calculate_tax(total) > 100;

The function is called for every row that passes the earlier filters. The optimizer cannot use an index on the computed value unless the function is marked IMMUTABLE and the expression is indexed.

The volatility

The volatility of the function determines whether the optimizer can cache the result, whether the function can be used in an index, and whether it can be used in a WHERE clause of an index.

Volatility	Meaning	Cacheable	Indexable
`IMMUTABLE`	Same input, same output	Yes	Yes
`STABLE`	Same input, same output within a statement	Yes	No
`VOLATILE`	The output can change	No	No

The calculate_tax function is IMMUTABLE because the tax rate is a constant. The order_total function is STABLE because the table can change between statements.

The function with a default parameter

CREATE OR REPLACE FUNCTION calculate_tax(
    amount NUMERIC,
    rate NUMERIC DEFAULT 0.19
)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT amount * rate;
$$;

The DEFAULT clause provides a default value for the parameter. The caller can omit the argument.

The function with a variadic parameter

A variadic parameter accepts a variable number of arguments.

CREATE OR REPLACE FUNCTION sum_all(VARIADIC nums NUMERIC[])
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(n), 0) FROM unnest(nums) AS n;
$$;

The function is called with any number of arguments.

The scalar function performance

A scalar function is called once per row. The overhead is the function invocation, which is measurable for a large table. The optimizer cannot inline the function in most databases, so the expression is evaluated row by row. The IMMUTABLE marker helps, but the function is still a black box to the optimizer.

EXPLAIN ANALYZE
SELECT id, calculate_tax(total) FROM orders;
-- The plan shows the function call for each row.

The scalar function vs the inline expression

For a simple expression, the inline form is faster because the optimizer can reason about it.

-- Inline: the optimizer sees the expression.
SELECT id, total * 0.19 FROM orders;

-- Function: the optimizer sees a black box.
SELECT id, calculate_tax(total) FROM orders;

The function is the right choice when the expression is complex, reused, or must be maintained in one place.

b. Table-valued functions

A table-valued function returns a set of rows. It is used in the FROM clause, where it behaves like a table.

The PostgreSQL syntax

CREATE OR REPLACE FUNCTION customer_orders(customer_id BIGINT)
RETURNS TABLE (
    order_id    BIGINT,
    order_date  DATE,
    total       NUMERIC
)
LANGUAGE sql
AS $$
    SELECT o.id, o.order_date, SUM(oi.quantity * p.price)
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.customer_id = customer_id
    GROUP BY o.id, o.order_date;
$$;

The RETURNS TABLE (...) declares the columns of the result. The function is called in the FROM clause.

SELECT *
FROM customer_orders(42);

The function returns the orders for the customer.

The SQL Server inline table-valued function

CREATE FUNCTION dbo.CustomerOrders(@customer_id INT)
RETURNS TABLE
AS
RETURN
(
    SELECT o.id AS order_id, o.order_date, SUM(oi.quantity * p.price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.customer_id = @customer_id
    GROUP BY o.id, o.order_date
);

The RETURNS TABLE with a single RETURN is the inline form. The optimizer expands the function into the query, which makes it as fast as a view or a derived table.

The SQL Server multi-statement table-valued function

CREATE FUNCTION dbo.CustomerOrdersMulti(@customer_id INT)
RETURNS @result TABLE (
    order_id    INT,
    order_date  DATE,
    total       DECIMAL(12,2)
)
AS
BEGIN
    INSERT INTO @result
    SELECT o.id, o.order_date, SUM(oi.quantity * p.price)
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.customer_id = @customer_id
    GROUP BY o.id, o.order_date;

    RETURN;
END;

The RETURNS @result TABLE declares a table variable, and the body inserts into it. The optimizer cannot expand the function, which makes it slower than the inline form.

The MySQL table-valued function

MySQL does not support table-valued functions directly. A stored procedure with a result set is the closest equivalent, but a procedure is not usable in a FROM clause. The workaround is a view with a parameter passed through a session variable, which is awkward and not recommended.

The table-valued function with a join

SELECT c.name, o.order_date, o.total
FROM customers c
CROSS JOIN LATERAL customer_orders(c.id) AS o
WHERE c.id IN (42, 99);

The LATERAL keyword allows the function to reference the columns of the preceding FROM item. The function is called once per customer, and the result is joined.

The table-valued function with a parameter

SELECT *
FROM customer_orders((SELECT id FROM customers WHERE email = 'alice@example.com'));

The parameter is a subquery. The function is called once with the result of the subquery.

The table-valued function with a generate_series

The generate_series function is the built-in table-valued function in PostgreSQL.

SELECT generate_series(1, 10) AS n;

The function returns ten rows. The user-defined function works the same way.

The table-valued function for unnesting

CREATE OR REPLACE FUNCTION split_tags(tags TEXT)
RETURNS TABLE (tag TEXT)
LANGUAGE sql
AS $$
    SELECT unnest(string_to_array(tags, ','));
$$;

The function splits a comma-separated string into rows. The query joins against the result.

SELECT p.id, t.tag
FROM products p
CROSS JOIN LATERAL split_tags(p.tags) AS t;

Each product’s tags are expanded into rows.

The inline vs multi-statement

Aspect	Inline	Multi-statement
Syntax	`RETURNS TABLE` with `RETURN`	`RETURNS @table TABLE` with `BEGIN`
Optimizer	Expands the function	Treats it as a black box
Performance	Same as a derived table	Slower
Complexity	Simple	Complex
Use case	The common case	The rare case

The inline form is the default. The multi-statement form is used when the logic cannot be expressed as a single query.

The table-valued function performance

The inline function is expanded into the calling query, and the optimizer reasons about the whole statement. The multi-statement function is a black box, and the optimizer estimates the row count without seeing the body. The difference is significant for a large result.

EXPLAIN ANALYZE
SELECT * FROM dbo.CustomerOrders(42);
-- The plan shows the expanded query.

The scalar vs table-valued

Aspect	Scalar	Table-valued
Returns	A single value	A set of rows
Used in	`SELECT`, `WHERE`	`FROM`
Optimizer	Black box	Expanded (inline)
Performance	Row-by-row	Set-based
Use case	A computed value	A row set

c. The limitations and the anti-patterns

User-defined functions have limitations that the developer must know. The limitations are different from the stored procedures.

The no-side-effects rule

A function must not modify the database state in most databases. The INSERT, the UPDATE, and the DELETE are prohibited in a function. The restriction is what makes the function safe to use in a query, because the query can be run multiple times without changing the database.

The transaction rule

A function runs in the transaction of the calling statement. It cannot start its own transaction, and it cannot roll back. The caller’s transaction is the only transaction.

The error-handling rule

A function cannot use TRY/CATCH in SQL Server or EXCEPTION in PostgreSQL in the same way a procedure can. The error handling is limited, and the function is expected to succeed or fail.

The result-set rule

A function returns a value or a table. It cannot return multiple result sets, and it cannot return a message.

The temp-table rule

A function cannot create a temporary table in most databases. The multi-statement table-valued function uses a table variable, which is the closest equivalent.

The dynamic-SQL rule

A function cannot use dynamic SQL in most databases, because the dynamic SQL could modify the state. The restriction is part of the no-side-effects rule.

The performance anti-pattern

A scalar function in a WHERE clause or a JOIN condition prevents the optimizer from using an index on the column. The function is evaluated for every row, and the index is not used.

-- ❌ The index on order_date is not used.
SELECT * FROM orders
WHERE YEAR(order_date) = 2026;

-- ✅ The index is used.
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

The same applies to a user-defined function.

-- ❌ The index on order_date is not used.
SELECT * FROM orders
WHERE order_year(order_date) = 2026;

The scalar-function-in-a-loop anti-pattern

A scalar function called per row is the equivalent of a loop. For a large table, the overhead is significant.

-- ❌ The function is called once per row.
SELECT id, calculate_tax(total) FROM orders;

-- ✅ The expression is inlined and vectorized.
SELECT id, total * 0.19 FROM orders;

The rule is to use a function when the expression is complex or reused, and to inline when the expression is simple.

The function-in-the-join anti-pattern

A function in a JOIN condition prevents the optimizer from using the join order and the indexes.

-- ❌ The join condition uses a function.
SELECT *
FROM orders o
JOIN customers c ON c.id = customer_id_from_order(o.id);

The rule is to put the function in the SELECT list or the WHERE clause, not in the JOIN condition.

The table-valued-function-in-the-WHERE anti-pattern

A table-valued function in the WHERE clause is called for every row.

-- ❌ The function is called once per order.
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customer_orders(o.customer_id));

The rule is to use the function in the FROM clause with LATERAL, or to rewrite the query as a join.

The limitations table

Limitation	Rule
Side effects	Prohibited
Transactions	The caller’s transaction
Error handling	Limited
Multiple result sets	Prohibited
Temp tables	Prohibited
Dynamic SQL	Prohibited
Index usage	The function prevents it

Complete Example Session

-- ============================================
-- PART 1: THE SCALAR FUNCTION
-- ============================================

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT amount * 0.19;
$$;

SELECT id, total, calculate_tax(total) AS tax
FROM orders
LIMIT 5;

-- ============================================
-- PART 2: THE FUNCTION WITH A CASE
-- ============================================

CREATE OR REPLACE FUNCTION order_status_label(status VARCHAR)
RETURNS VARCHAR
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE status
        WHEN 'pending' THEN 'Pending review'
        WHEN 'shipped' THEN 'In transit'
        WHEN 'delivered' THEN 'Complete'
        ELSE 'Unknown'
    END;
$$;

SELECT id, status, order_status_label(status) AS label
FROM orders;

-- ============================================
-- PART 3: THE FUNCTION WITH A SUBQUERY
-- ============================================

CREATE OR REPLACE FUNCTION order_total(order_id BIGINT)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(quantity * price), 0)
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = order_id;
$$;

SELECT id, order_total(id) AS total
FROM orders
WHERE id IN (1, 2, 3);

-- ============================================
-- PART 4: THE TABLE-VALUED FUNCTION
-- ============================================

CREATE OR REPLACE FUNCTION customer_orders(customer_id BIGINT)
RETURNS TABLE (
    order_id    BIGINT,
    order_date  DATE,
    total       NUMERIC
)
LANGUAGE sql
STABLE
AS $$
    SELECT o.id, o.order_date, SUM(oi.quantity * p.price)
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.customer_id = customer_id
    GROUP BY o.id, o.order_date;
$$;

SELECT * FROM customer_orders(42);

-- ============================================
-- PART 5: THE LATERAL JOIN
-- ============================================

SELECT c.name, o.order_date, o.total
FROM customers c
CROSS JOIN LATERAL customer_orders(c.id) AS o
WHERE c.id IN (42, 99);

-- ============================================
-- PART 6: THE UNNESTING FUNCTION
-- ============================================

CREATE OR REPLACE FUNCTION split_tags(tags TEXT)
RETURNS TABLE (tag TEXT)
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT unnest(string_to_array(tags, ','));
$$;

SELECT p.id, t.tag
FROM products p
CROSS JOIN LATERAL split_tags(p.tags) AS t;

-- ============================================
-- PART 7: THE VOLATILITY
-- ============================================

-- IMMUTABLE: same input, same output
CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT amount * 0.19;
$$;

-- STABLE: same input, same output within a statement
CREATE OR REPLACE FUNCTION order_total(order_id BIGINT)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(quantity * price), 0)
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = order_id;
$$;

-- VOLATILE: the output can change
CREATE OR REPLACE FUNCTION random_discount()
RETURNS NUMERIC
LANGUAGE sql
VOLATILE
AS $$
    SELECT random() * 0.1;
$$;

-- ============================================
-- PART 8: THE INDEX INTERACTION
-- ============================================

-- ❌ The index is not used.
SELECT * FROM orders
WHERE order_year(order_date) = 2026;

-- ✅ The index is used.
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- ============================================
-- PART 9: THE PERFORMANCE
-- ============================================

-- ❌ The function is called once per row.
EXPLAIN ANALYZE
SELECT id, calculate_tax(total) FROM orders;

-- ✅ The expression is inlined.
EXPLAIN ANALYZE
SELECT id, total * 0.19 FROM orders;

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- Scalar function: a computed value.
-- Table-valued function: a set of rows.
-- IMMUTABLE: the function can be cached and indexed.
-- STABLE: the function is cached within a statement.
-- VOLATILE: the function is re-evaluated.
-- Use the function in the SELECT list, not in the WHERE or JOIN.
-- Use LATERAL to call the function once per row of a FROM item.

The ten parts showed the scalar function, the function with a CASE, the function with a subquery, the table-valued function, the lateral join, the unnesting function, the volatility, the index interaction, the performance, and the complete pattern.

Quick Reference

The Scalar Function Syntax

Database	Syntax
PostgreSQL	`CREATE FUNCTION name(args) RETURNS type LANGUAGE sql AS $$ ... $$`
SQL Server	`CREATE FUNCTION name(@arg type) RETURNS type AS BEGIN RETURN ...; END;`
MySQL	`CREATE FUNCTION name(arg type) RETURNS type RETURN ...`

The Table-Valued Function Syntax

Database	Syntax
PostgreSQL	`RETURNS TABLE (col type, ...) LANGUAGE sql AS $$ ... $$`
SQL Server (inline)	`RETURNS TABLE AS RETURN (SELECT ...)`
SQL Server (multi)	`RETURNS @t TABLE (...) AS BEGIN INSERT ...; RETURN; END;`
MySQL	Not supported

The Volatility

Marker	Cacheable	Indexable	Use Case
`IMMUTABLE`	Yes	Yes	Constant results
`STABLE`	Within a statement	No	Reads tables
`VOLATILE`	No	No	Random, time-dependent

The Scalar vs Table-Valued

Aspect	Scalar	Table-Valued
Returns	Single value	Row set
Used in	`SELECT`, `WHERE`	`FROM`
Optimizer	Black box	Expanded (inline)
Performance	Row-by-row	Set-based

The Anti-Patterns

Anti-pattern	Problem
Function in `WHERE`	Prevents index usage
Function in `JOIN`	Prevents join optimization
Function per row	Row-by-row overhead
Side effects	Prohibited
Dynamic SQL	Prohibited

Best Practices

✅ Do This:

-- Mark the function IMMUTABLE when the result is constant.
CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$ SELECT amount * 0.19; $$;  -- ✅

-- Use the function in the SELECT list.
SELECT id, calculate_tax(total) FROM orders;  -- ✅

-- Use the table-valued function in the FROM clause.
SELECT * FROM customer_orders(42);  -- ✅

-- Use LATERAL to call the function per row.
SELECT c.name, o.total
FROM customers c
CROSS JOIN LATERAL customer_orders(c.id) AS o;  -- ✅

-- Use the inline table-valued function for performance.
RETURNS TABLE AS RETURN (SELECT ...);  -- ✅

-- Test the function in isolation.
SELECT calculate_tax(100);  -- ✅

❌ Don’t Do This:

-- Don't use a function in a WHERE clause on an indexed column.
SELECT * FROM orders WHERE order_year(order_date) = 2026;  -- ❌

-- Don't use a function in a JOIN condition.
SELECT * FROM orders o JOIN customers c ON c.id = f(o.id);  -- ❌

-- Don't call a function per row when the expression is simple.
SELECT id, calculate_tax(total) FROM orders;  -- ⚠️ If the expression is trivial

-- Don't use a function with side effects.
CREATE FUNCTION f() RETURNS INT AS $$ INSERT INTO t VALUES (1); SELECT 1; $$;  -- ❌

-- Don't use dynamic SQL in a function.
CREATE FUNCTION f() RETURNS INT AS $$ EXECUTE 'DELETE FROM t'; $$;  -- ❌

-- Don't use a multi-statement function for a simple query.
RETURNS @t TABLE AS BEGIN INSERT ...; RETURN; END;  -- ⚠️ Use the inline form

Common Pitfalls

Pitfall	Why It Happens	Fix
Index not used	Function in `WHERE`	Use the range predicate
Slow query	Scalar function per row	Inline the expression
Function is not immutable	Missing the marker	Add `IMMUTABLE`
Side effect attempted	`INSERT` in a function	Use a procedure
Table-valued function missing	MySQL	Use a stored procedure
Inline not used	Multi-statement syntax	Use `RETURNS TABLE`
Lateral missing	The function references the outer table	Add `CROSS JOIN LATERAL`

Real-World Examples

1. Scalar Function

CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$ SELECT amount * 0.19; $$;

2. Function with CASE

CREATE FUNCTION order_status_label(status VARCHAR)
RETURNS VARCHAR
LANGUAGE sql
IMMUTABLE
AS $$ SELECT CASE status WHEN 'pending' THEN 'Pending' ELSE 'Other' END; $$;

3. Table-Valued Function

CREATE FUNCTION customer_orders(customer_id BIGINT)
RETURNS TABLE (order_id BIGINT, total NUMERIC)
LANGUAGE sql
STABLE
AS $$ SELECT id, SUM(total) FROM orders WHERE customer_id = customer_id GROUP BY id; $$;

4. Lateral Join

SELECT c.name, o.total
FROM customers c
CROSS JOIN LATERAL customer_orders(c.id) AS o;

5. Unnesting

CREATE FUNCTION split_tags(tags TEXT)
RETURNS TABLE (tag TEXT)
LANGUAGE sql
IMMUTABLE
AS $$ SELECT unnest(string_to_array(tags, ',')); $$;

6. Volatility

CREATE FUNCTION f() RETURNS INT LANGUAGE sql IMMUTABLE AS $$ SELECT 1; $$;

7. Default Parameter

CREATE FUNCTION calculate_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.19)
RETURNS NUMERIC LANGUAGE sql AS $$ SELECT amount * rate; $$;

8. SQL Server Inline

CREATE FUNCTION dbo.CustomerOrders(@id INT)
RETURNS TABLE
AS RETURN (SELECT * FROM orders WHERE customer_id = @id);

9. SQL Server Multi-Statement

CREATE FUNCTION dbo.CustomerOrdersMulti(@id INT)
RETURNS @t TABLE (id INT, total DECIMAL(12,2))
AS BEGIN INSERT INTO @t SELECT ...; RETURN; END;

10. The Index Interaction

-- ❌ WHERE order_year(order_date) = 2026
-- ✅ WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

Visual

The Scalar Function

┌────────────────────────────────────────────────────────────┐
│  THE SCALAR FUNCTION                                       │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CREATE FUNCTION calculate_tax(amount NUMERIC)    │     │
│  │  RETURNS NUMERIC                                  │     │
│  │  LANGUAGE sql                                     │     │
│  │  IMMUTABLE                                        │     │
│  │  AS $$ SELECT amount * 0.19; $$;                  │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           │  Called in the SELECT list    │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  SELECT id, calculate_tax(total) AS tax           │     │
│  │  FROM orders;                                     │     │
│  │                                                   │     │
│  │  Each row produces one value.                     │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Table-Valued Function

┌────────────────────────────────────────────────────────────┐
│  THE TABLE-VALUED FUNCTION                                 │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CREATE FUNCTION customer_orders(customer_id)     │     │
│  │  RETURNS TABLE (order_id BIGINT, total NUMERIC)   │     │
│  │  LANGUAGE sql                                     │     │
│  │  AS $$ SELECT id, SUM(total) FROM orders ... $$;  │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           │  Called in the FROM clause    │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  SELECT * FROM customer_orders(42);               │     │
│  │                                                   │     │
│  │  The function returns a row set.                  │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Volatility

┌────────────────────────────────────────────────────────────┐
│  THE VOLATILITY                                            │
│                                                            │
│  IMMUTABLE                                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Same input, same output, always.                 │     │
│  │  The optimizer can cache the result.              │     │
│  │  The result can be used in an index.              │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  STABLE                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Same input, same output within a statement.      │     │
│  │  The function reads a table.                      │     │
│  │  The result can be cached within the statement.   │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  VOLATILE                                                  │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The output can change between calls.             │     │
│  │  The function reads a random value or the time.   │     │
│  │  No caching.                                      │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Inline vs Multi-Statement

┌────────────────────────────────────────────────────────────┐
│  INLINE vs MULTI-STATEMENT                                 │
│                                                            │
│  INLINE                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  RETURNS TABLE AS RETURN (SELECT ...)             │     │
│  │                                                   │     │
│  │  The optimizer expands the function.              │     │
│  │  The performance is the same as a derived table.  │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  MULTI-STATEMENT                                           │
│  ┌──────────────────────────────────────────────────┐     │
│  │  RETURNS @t TABLE (...)                           │     │
│  │  AS BEGIN INSERT INTO @t SELECT ...; RETURN; END; │     │
│  │                                                   │     │
│  │  The optimizer treats the function as a black box.│     │
│  │  The performance is slower.                       │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Scalar function	Returns a single value
Table-valued function	Returns a set of rows
`IMMUTABLE`	Same input, same output
`STABLE`	Same output within a statement
`VOLATILE`	The output can change
Scalar use	`SELECT`, `WHERE`
Table-valued use	`FROM`
`LATERAL`	Calls the function per row
Inline	The optimizer expands it
Multi-statement	The optimizer treats it as a black box

Key takeaways:

A scalar function returns a single value. It is used in a column expression, a WHERE clause, or any place a value is expected. The function is called once per row, and the overhead is measurable for a large table.
A table-valued function returns a set of rows. It is used in the FROM clause, where it behaves like a table. The LATERAL keyword allows the function to reference the columns of the preceding FROM item.
The volatility marker determines the caching and the indexing. The IMMUTABLE marker allows the optimizer to cache the result and to use the function in an index. The STABLE marker allows caching within a statement. The VOLATILE marker allows neither.
A function must not modify the database state. The INSERT, the UPDATE, and the DELETE are prohibited in a function. The restriction is what makes the function safe to use in a query.
A function in a WHERE clause prevents index usage. The optimizer cannot use an index on a column when the column is wrapped in a function. The range predicate is the alternative.
The inline table-valued function is faster than the multi-statement form. The inline form is expanded into the calling query, and the optimizer reasons about the whole statement. The multi-statement form is a black box.
The scalar function per row is the equivalent of a loop. For a large table, the overhead is significant. The inline expression is the alternative when the expression is simple.
The function is the unit of reuse. The expression is captured once, and the queries call the name. The result is a schema that is easier to understand, easier to test, and easier to maintain.

Remember: The user-defined function is the reusable unit of logic in a database. The scalar function returns a value, and the table-valued function returns a row set. The volatility marker tells the optimizer how to treat the function. The function must not modify the state, and it must be used in the right place in the query. The result is a schema where the complex expressions are named, tested, and reused, and where the queries read as a composition of the functions and the tables.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!