🧮User-defined functions

Scalar, table-valued, or pipeline glue

UDFs package reusable logic: scalar functions return one value per invocation; table-valued functions return row sets you can join like views. Implementation language (SQL, PL/pgSQL, T-SQL, Java on some engines) and volatility flags change both correctness and whether the optimizer can inline or index through them.

SQL

Immutable scalar function (index-friendly in PostgreSQL)

CREATE OR REPLACE FUNCTION core.normalize_gene(sym text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $ SELECT upper(trim(sym)); $;

SELECT core.normalize_gene('  brca1 ') AS gene;

✓Illustrative result set

gene
BRCA1

🧪Volatility (PostgreSQL)

Tell the planner what it can cache or reorder.

Mark	Meaning
IMMUTABLE	Same inputs always yield the same outputs, pure, safe to use in indexes when the expression qualifies.
STABLE	Fixed for all rows within a single statement snapshot, for example `current_timestamp` in a transaction.
VOLATILE	Default: may read or write tables, depend on session state, or return different results each call.

Opaque scalar UDFs on hot paths

Per-row scalar UDFs that prevent parallelism or hide I/O from the planner (common pain on SQL Server).
Side effects or hidden queries inside functions referenced from SELECT.
Marking functions IMMUTABLE when they read mutable state, wrong answers and unsafe indexes.

Inline SQL or table-valued patterns

Benchmark TVFs and inlined SQL on representative plans and row counts.
Match volatility to reality; document non-deterministic helpers explicitly.
Version function definitions with migrations like any other API.

Design split

Push stable, row-local transforms into SQL-language functions; keep network-heavy or policy-rich flows in application services unless you have a strong reason to execute them inside the database.

TL;DR

User-defined functions package reusable scalar or table expressions inside the database.SQL functions are often inlined for optimization; PL/pgSQL / T-SQL procedures with side effects are not. Volatility (IMMUTABLE/STABLE/VOLATILE in PostgreSQL) affects index usage and planner caching, mark truthfully. Prefer built-ins for performance-critical paths unless profiling shows a UDF wins.