How to CREATE FUNCTION (UDFs) in PostgreSQL

How do I create and use user-defined functions (UDFs) in PostgreSQL?

CREATE FUNCTION lets you define reusable user-defined functions (UDFs) in SQL or PL/pgSQL, extending PostgreSQL with custom logic.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a PostgreSQL UDF?

A user-defined function (UDF) is an SQL or PL/pgSQL block you store in the database and call like any built-in function. UDFs encapsulate business logic, reduce duplication, and speed up complex calculations.

When should I create a function instead of a view or query?

Create a UDF when you need parameters, conditional logic, loops, or the ability to return scalars, rows, or sets on demand. Views are static SELECTs and cannot accept parameters.

How do I write a simple SQL UDF?

Use CREATE FUNCTION, declare parameters and the return type, then wrap a single SQL statement in $$ delimiters using LANGUAGE sql.

Example: total spent by customer

This SQL UDF returns the lifetime spend of one customer from the Orders table.

CREATE OR REPLACE FUNCTION total_spent(customer_id INT) RETURNS NUMERIC(12,2) AS $$ SELECT COALESCE(SUM(total_amount),0) FROM Orders WHERE customer_id = $1; $$ LANGUAGE sql IMMUTABLE;

How do I create a PL/pgSQL UDF with control flow?

Choose LANGUAGE plpgsql. You can declare variables, use IF, loops, and raise notices. Useful for multi-step procedures.

Example: restock a product

CREATE OR REPLACE FUNCTION restock_product(p_id INT, add_qty INT) RETURNS VOID AS $$ BEGIN UPDATE Products SET stock = stock + add_qty WHERE id = p_id; INSERT INTO InventoryLog(product_id, qty_added, logged_at) VALUES (p_id, add_qty, NOW()); END; $$ LANGUAGE plpgsql;

How do I call a UDF?

Invoke it in any SELECT, INSERT, UPDATE, or as a standalone statement: SELECT total_spent(42);

Best practices for UDFs

Mark functions IMMUTABLE or STABLE when possible for planner optimizations. Add STRICT to auto-return NULL on any NULL parameter. Version functions with OR REPLACE to avoid drop/recreate downtime.

How do I debug or modify a function?

Use CREATE OR REPLACE FUNCTION to update logic. Add RAISE NOTICE statements in PL/pgSQL for logging. pgAdmin and psql -E help inspect compiled source.

Can a function return a table?

Yes, declare RETURNS TABLE(col1 type, ...) or RETURNS SETOF. Query it like a regular table: SELECT * FROM top_customers();

Why How to CREATE FUNCTION (UDFs) in PostgreSQL is important

How to CREATE FUNCTION (UDFs) in PostgreSQL Example Usage


-- Show each customer’s total spend using the UDF defined above
SELECT c.id, c.name, total_spent(c.id) AS lifetime_value
FROM Customers c
ORDER BY lifetime_value DESC;

How to CREATE FUNCTION (UDFs) in PostgreSQL Syntax


CREATE [OR REPLACE] FUNCTION name ( [ [argname] argtype [DEFAULT default_expr] [, ...] ] )
RETURNS rettype
[ LANGUAGE { sql | plpgsql | c | internal | ... } ]
[ IMMUTABLE | STABLE | VOLATILE ]
[ STRICT ]
[ SECURITY { DEFINER | INVOKER } ]
AS $$
    -- SQL or PL/pgSQL body referencing parameters as $1, $2, ...
$$;

-- Ecommerce example: return order items for one order
CREATE FUNCTION list_order_items(p_order INT)
RETURNS TABLE(product_name TEXT, qty INT, line_total NUMERIC) AS $$
  SELECT p.name, oi.quantity, oi.quantity * p.price
  FROM OrderItems oi
  JOIN Products p ON p.id = oi.product_id
  WHERE oi.order_id = $1;
$$ LANGUAGE sql STABLE;

Common Mistakes

Using VOLATILE when the function is actually IMMUTABLE. This prevents the planner from caching results. Declare IMMUTABLE if the function always returns the same output for identical inputs.
Forgetting STRICT on functions that should return NULL when any input is NULL. Without STRICT, you must manually handle nulls, leading to unexpected results.

Frequently Asked Questions (FAQs)

Can I drop a function without breaking dependencies?

Add IF EXISTS to avoid an error, or use CREATE OR REPLACE so callers stay intact.

What languages can I use besides SQL and PL/pgSQL?

PostgreSQL supports PL/Python, PL/Perl, PL/V8, and more after installing extensions.

Do UDFs hurt performance?

SQL UDFs are inlined by the planner when possible. Marking functions IMMUTABLE and STRICT helps the optimizer. PL/pgSQL incurs minimal overhead for most workloads.