User-defined functions are, in my experience, the single most-used piece of PostgreSQL’s extensibility toolkit — far more common in everyday work than custom operators, custom aggregates, or even triggers. Nearly every PostgreSQL database I’ve worked on has at least a handful of functions handling validation, calculated fields, or reusable query logic. Once I got comfortable writing them, I found myself reaching for a function almost every time I noticed the same piece of SQL logic being copy-pasted across multiple queries.
In this guide, I’ll cover what user-defined functions are, the core syntax, how parameters and return types work, several practical real-world examples, function volatility and its performance implications, common troubleshooting scenarios, and the best practices I follow when writing and maintaining them.
What Is a User-Defined Function?
A user-defined function (UDF) in PostgreSQL is a named, reusable block of logic that accepts zero or more parameters, executes a defined body, and returns a value — whether that’s a single scalar value, a row, or an entire set of rows. Functions can be written in SQL directly, in PL/pgSQL, or in any of PostgreSQL’s other supported procedural languages (PL/Python, PL/Perl, and so on, as covered in other guides).
Functions are called either directly in a SELECT statement, as part of a WHERE clause, inside a FROM clause (for set-returning functions), or from within other functions and triggers.
Basic Syntax
CREATE OR REPLACE FUNCTION function_name(parameter_list)
RETURNS return_type
LANGUAGE language_name
AS $$
-- function body
$$;
The simplest possible functions can even be written directly in plain SQL, without PL/pgSQL at all:
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE sql
AS $$
SELECT a + b;
$$;
SELECT add_numbers(4, 9);
-- 13
SQL-language functions like this are worth knowing about specifically because PostgreSQL can sometimes inline them directly into the calling query’s execution plan, which makes them effectively free compared to a PL/pgSQL function call, especially for simple, single-expression logic.
Parameters: Positional, Named, and Default Values
CREATE OR REPLACE FUNCTION calculate_total(price NUMERIC, tax_rate NUMERIC DEFAULT 0.08)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN price + (price * tax_rate);
END;
$$;
SELECT calculate_total(100);
-- 108.00
SELECT calculate_total(100, 0.15);
-- 115.00
SELECT calculate_total(price := 100, tax_rate := 0.05);
-- 105.00
Default parameter values and named parameter syntax (param := value) make functions genuinely flexible to call, especially once a function accumulates several optional parameters over time.
Returning a Single Scalar Value
CREATE OR REPLACE FUNCTION full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(first_name) || ' ' || trim(last_name);
END;
$$;
SELECT full_name(' Ahmed ', ' Khan ');
-- Ahmed Khan
Returning a Single Row (Composite Type)
CREATE TYPE order_summary AS (
total_items INTEGER,
total_price NUMERIC
);
CREATE OR REPLACE FUNCTION get_order_summary(p_order_id INTEGER)
RETURNS order_summary
LANGUAGE plpgsql
AS $$
DECLARE
result order_summary;
BEGIN
SELECT COUNT(*), SUM(price * quantity)
INTO result.total_items, result.total_price
FROM order_items
WHERE order_id = p_order_id;
RETURN result;
END;
$$;
SELECT * FROM get_order_summary(101);
Returning a Set of Rows
CREATE OR REPLACE FUNCTION get_customers_by_status(p_status TEXT)
RETURNS TABLE(id INTEGER, name TEXT, email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT c.id, c.name, c.email
FROM customers c
WHERE c.status = p_status;
END;
$$;
SELECT * FROM get_customers_by_status('active');
Functions returning TABLE(...) or SETOF can be used directly in the FROM clause of another query, joined against other tables just like a regular view:
SELECT c.name, o.order_count
FROM get_customers_by_status('active') c
JOIN (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) o
ON o.customer_id = c.id;
Function Volatility: IMMUTABLE, STABLE, and VOLATILE
Every PostgreSQL function has a volatility category, which tells the query planner how aggressively it can optimize calls to that function:
IMMUTABLE— the function always returns the same result for the same input arguments, with no dependency on database state. Safe to use in index expressions and to evaluate once and cache within a query.STABLE— the function doesn’t modify the database and returns the same result for the same arguments within a single statement, but may return different results across different statements (for example, a function readingcurrent_setting()).VOLATILE(the default) — the function can return different results even within the same statement, or has side effects like modifying data. PostgreSQL can’t optimize around this and must call it fresh every time.
CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_percent NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
RETURN price - (price * discount_percent / 100);
END;
$$;
Getting volatility right isn’t just a formality — it has real performance implications. An IMMUTABLE function used inside a WHERE clause on an indexed expression can be used to build a functional index, while a mislabeled VOLATILE function used the same way would silently prevent that optimization, or worse, a mislabeled IMMUTABLE function whose output actually changes could produce silently incorrect query results if PostgreSQL caches a stale value.
Functional Indexes Using IMMUTABLE Functions
CREATE OR REPLACE FUNCTION normalize_phone(phone TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
SELECT regexp_replace(phone, '[^0-9]', '', 'g');
$$;
CREATE INDEX idx_customers_normalized_phone ON customers (normalize_phone(phone));
SELECT * FROM customers WHERE normalize_phone(phone) = '13105551234';
This lets me search efficiently on a normalized version of a messy phone-number column without needing a separate stored/generated column, as long as the underlying function is properly marked IMMUTABLE.
Function Overloading
PostgreSQL supports overloading — multiple functions sharing the same name but differing in parameter types or count:
CREATE OR REPLACE FUNCTION format_price(amount NUMERIC)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
SELECT '$' || to_char(amount, 'FM999,999,990.00');
$$;
CREATE OR REPLACE FUNCTION format_price(amount NUMERIC, currency_symbol TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
SELECT currency_symbol || to_char(amount, 'FM999,999,990.00');
$$;
SELECT format_price(1500);
-- $1,500.00
SELECT format_price(1500, '€');
-- €1,500.00
PostgreSQL resolves which overload to call based on the number and types of arguments provided at the call site.
Variadic Functions
For functions that need to accept a variable number of arguments:
CREATE OR REPLACE FUNCTION sum_all(VARIADIC values NUMERIC[])
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
total NUMERIC := 0;
v NUMERIC;
BEGIN
FOREACH v IN ARRAY values LOOP
total := total + v;
END LOOP;
RETURN total;
END;
$$;
SELECT sum_all(1, 2, 3, 4, 5);
-- 15
Common Use Cases
- Reusable calculated fields — tax calculations, formatted display strings, derived scores.
- Validation logic shared between multiple triggers or application entry points.
- Functional indexes on normalized or transformed versions of a column.
- Encapsulating complex joins or aggregations as a callable, parameterized query, similar to a parameterized view.
- API-layer abstraction — some teams intentionally expose a defined set of database functions as the only interface application code is allowed to call, keeping the underlying schema free to change without breaking consumers.
Troubleshooting Common Issues
“function is not unique” error when calling an overloaded function This happens when PostgreSQL can’t determine which overload to use, usually because the argument types are ambiguous (for example, passing an untyped NULL). I add an explicit type cast to the argument, like NULL::numeric, to resolve the ambiguity.
Function returns stale or incorrect cached results This is almost always a volatility mislabeling issue — a function marked IMMUTABLE that actually depends on something other than its direct input arguments (like reading from a table that changes) can produce incorrect, cached results in some query contexts. I re-examine the function body carefully and downgrade its volatility marker to STABLE or VOLATILE if it isn’t a pure function of its arguments.
“cannot change return type of existing function” error on CREATE OR REPLACE CREATE OR REPLACE FUNCTION can’t change certain aspects of an existing function’s signature, including its return type or parameter names in some cases. I use DROP FUNCTION followed by CREATE FUNCTION instead when I need to make a structural change like this, which requires recreating any dependent objects (like views) afterward.
Slow performance from calling a function per-row in a large query VOLATILE (and to a lesser extent STABLE) functions called against every row of a large result set can be significantly slower than an equivalent set-based SQL expression, since PostgreSQL generally can’t optimize away repeated calls. I check whether the function’s logic could be rewritten as a plain SQL expression inline, or as an SQL-language (not PL/pgSQL) function, which the planner has a better chance of inlining.
Function works in isolation but fails inside a larger query This is often a search_path issue — if a function references an unqualified table name and gets installed in a database where multiple schemas exist, the function might resolve to the wrong table depending on the calling session’s search_path. I qualify table and function references explicitly with their schema name inside function bodies whenever there’s any ambiguity risk.
Best Practices
- Choose the correct volatility category deliberately, every time. This is one of the most under-appreciated correctness and performance levers available for PostgreSQL functions.
- Prefer plain SQL-language functions for simple, single-expression logic. They’re more likely to be inlined by the planner than PL/pgSQL equivalents, which can meaningfully improve performance for functions called extremely often.
- Use
SET search_pathon security-sensitive functions. For functions owned by a privileged role, I explicitly pin the search path (SET search_path = public, pg_temp) to avoid a class of privilege-escalation vulnerability where a malicious user could create objects earlier in the search path to hijack unqualified references. - Name functions clearly and consistently. A function name should make its purpose and return type obvious without needing to open its definition —
get_active_customers()versus something vague likeprocess_data(). - Document parameters and return values. I use
COMMENT ON FUNCTIONto record what each function does directly in the database catalog, which is genuinely useful when exploring an unfamiliar schema later with\df+inpsql. - Avoid overly broad, multi-purpose functions. A function that does three unrelated things based on a mode flag parameter is harder to test, optimize, and reason about than three small, focused functions.
- Version control every function definition alongside the rest of the schema, and review changes to shared functions with the same scrutiny as any other production code change, since a single shared function can silently affect dozens of call sites at once.
- Test functions independently before relying on them in triggers or larger procedures. I write direct
SELECT function_name(...)test cases covering typical inputs, edge cases, andNULLhandling before wiring the function into more complex logic.
Function Cost and Row Estimates
For functions that return sets, particularly those used in FROM clauses joined against other tables, PostgreSQL’s planner benefits from hints about expected execution cost and row counts:
CREATE OR REPLACE FUNCTION get_customers_by_status(p_status TEXT)
RETURNS TABLE(id INTEGER, name TEXT, email TEXT)
LANGUAGE plpgsql
STABLE
ROWS 100
COST 50
AS $$
BEGIN
RETURN QUERY
SELECT c.id, c.name, c.email
FROM customers c
WHERE c.status = p_status;
END;
$$;
ROWS tells the planner roughly how many rows to expect back, which materially affects join strategy selection when this function’s results are joined against other tables. COST gives the planner a relative sense of how expensive the function is per call, compared to the default cost unit for a simple operation. I don’t obsess over getting these numbers perfectly precise, but providing a reasonable estimate — rather than leaving the planner to guess with generic defaults — noticeably improves plan quality for functions used inside larger queries.
Dropping and Modifying Functions
DROP FUNCTION IF EXISTS calculate_total(NUMERIC, NUMERIC);
Because PostgreSQL allows overloading, I always include the parameter types when dropping a function, since DROP FUNCTION calculate_total alone is ambiguous if multiple overloads exist.
CREATE OR REPLACE FUNCTION works for most changes that don’t alter the function’s signature or return type. For genuine signature changes, I drop and recreate, and I check for dependent objects — views, other functions, or generated columns depending on the function — before doing so, using CASCADE deliberately rather than by default.
Inspecting Existing Functions
\df+ get_order_summary
Or querying the catalog directly:
SELECT proname, prosrc, provolatile, prorettype::regtype
FROM pg_proc
WHERE proname = 'get_order_summary';
The provolatile column (i for immutable, s for stable, v for volatile) is one of the first things I check when debugging unexpected caching or performance behavior involving an unfamiliar function I didn’t write myself.
Frequently Asked Questions
What’s the difference between a function and a stored procedure? Functions always return a value (even if just void) and execute within the caller’s transaction; procedures, invoked with CALL, can manage their own internal transactions with COMMIT and ROLLBACK, but don’t return a value directly — only through OUT parameters. A separate guide in this series covers procedures in depth.
Can a function have side effects, like inserting into a table? Yes — a function marked VOLATILE (the default) can freely perform INSERT, UPDATE, or DELETE operations. I just make sure the volatility marker accurately reflects this, since marking a function with side effects as IMMUTABLE or STABLE would be incorrect and could lead to the planner skipping or caching calls it shouldn’t.
How do I pass an array of composite types into a function? I declare the parameter type as an array of the composite type (my_type[]), and construct the argument at the call site using ARRAY[ROW(...)::my_type, ROW(...)::my_type] syntax, which PostgreSQL accepts directly as a function argument.
Can I write a recursive function in PL/pgSQL? Yes — a PL/pgSQL function can call itself, and PostgreSQL handles this correctly as long as there’s a proper base case to terminate recursion. For recursive queries over table data specifically, though, a WITH RECURSIVE common table expression is usually a better fit than a recursive procedural function.
When to Keep Logic Out of Functions
Not everything belongs in a database function, even when it could technically live there. Logic that changes frequently, that’s specific to a single application’s presentation needs, or that would benefit from the testing and deployment tooling available at the application layer is often better left there. I reserve database functions for logic that genuinely needs to be shared consistently across multiple consumers of the data, or that benefits meaningfully from running close to the data itself — validation, calculated fields used by multiple queries, and functional indexes are the clearest cases where the tradeoff favors putting logic inside the database.
Final Thoughts
User-defined functions are the connective tissue of a well-organized PostgreSQL schema — they let me encapsulate logic once and reuse it consistently everywhere it’s needed, from queries to triggers to other functions. The biggest lesson I’ve internalized over years of writing them is that small details like volatility markers and SQL-vs-PL/pgSQL language choice aren’t just style preferences; they have real, sometimes significant, effects on both correctness and query performance. Getting comfortable with the full range of what functions can do — scalar returns, table returns, overloading, variadic arguments, functional indexes — genuinely changes how I think about database design, turning PostgreSQL from a passive data store into an active, programmable part of the system.