How to Use Stored Functions in PostgreSQL

Key Insights

• PostgreSQL functions encapsulate business logic at the database layer, improving performance by reducing network round-trips and enabling query optimization that application code cannot achieve.
• Choose PL/pgSQL for complex procedural logic with variables and control flow, but use plain SQL functions when possible—they’re faster and allow the query planner to inline them.
• Function volatility categories (IMMUTABLE, STABLE, VOLATILE) aren’t just documentation—they directly impact query optimization, indexing capabilities, and parallel query execution.

Introduction to PostgreSQL Functions

Stored functions in PostgreSQL are reusable blocks of code that execute on the database server. They accept parameters, perform operations, and return results—all without leaving the database context. This matters because every network round-trip between your application and database adds latency. A function that performs ten queries internally executes faster than making ten separate calls from your application.

Functions also centralize business logic. When your discount calculation lives in a function rather than scattered across multiple services, you change it once instead of hunting through codebases. PostgreSQL can also optimize function calls in ways impossible for external code, particularly when functions are marked with appropriate volatility settings.

Use functions for data validation, complex calculations, and operations requiring multiple queries. Don’t use them for simple CRUD operations or logic that benefits from your application’s testing infrastructure and deployment pipeline.

Creating Basic Functions

The CREATE FUNCTION syntax requires a name, parameters, return type, language, and function body. Here’s a straightforward example:

CREATE FUNCTION calculate_discount(
    original_price NUMERIC,
    discount_percent NUMERIC
) RETURNS NUMERIC AS $$
    SELECT original_price * (1 - discount_percent / 100);
$$ LANGUAGE SQL;

-- Usage
SELECT calculate_discount(100.00, 15); -- Returns 85.00

The $$ delimiter marks the function body boundaries. You can use other delimiters, but $$ is convention. The LANGUAGE SQL clause indicates this function contains a single SQL statement.

Functions can contain more complex logic with multiple parameters:

CREATE FUNCTION apply_tiered_discount(
    price NUMERIC,
    quantity INTEGER,
    customer_tier TEXT
) RETURNS NUMERIC AS $$
    SELECT CASE
        WHEN customer_tier = 'premium' THEN price * 0.80
        WHEN customer_tier = 'standard' AND quantity > 10 THEN price * 0.90
        WHEN quantity > 50 THEN price * 0.95
        ELSE price
    END;
$$ LANGUAGE SQL;

Function Language Options

PostgreSQL supports multiple procedural languages. SQL functions contain declarative queries, while PL/pgSQL provides procedural capabilities like variables and control structures. For most use cases, these two cover your needs.

SQL functions are simpler and faster. The query planner can inline them, treating the function body as part of the calling query. This enables optimizations impossible with procedural languages:

CREATE FUNCTION get_active_users_sql()
RETURNS TABLE(user_id INT, email TEXT) AS $$
    SELECT id, email FROM users WHERE active = true;
$$ LANGUAGE SQL;

The equivalent PL/pgSQL function requires procedural syntax:

CREATE FUNCTION get_active_users_plpgsql()
RETURNS TABLE(user_id INT, email TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, email FROM users WHERE active = true;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL shines when you need variables, conditional logic, or loops:

CREATE FUNCTION calculate_order_total(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    subtotal NUMERIC := 0;
    tax_rate NUMERIC := 0.08;
    shipping NUMERIC := 0;
    total NUMERIC;
BEGIN
    -- Calculate subtotal
    SELECT SUM(price * quantity) INTO subtotal
    FROM order_items
    WHERE order_id = calculate_order_total.order_id;
    
    -- Determine shipping
    IF subtotal > 100 THEN
        shipping := 0;
    ELSE
        shipping := 10;
    END IF;
    
    -- Calculate total
    total := subtotal + (subtotal * tax_rate) + shipping;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Use SQL functions by default. Switch to PL/pgSQL only when you need procedural features.

Working with Different Return Types

Functions can return scalar values, complete tables, or multiple values through OUT parameters. Scalar returns are straightforward—you’ve seen them in previous examples.

Table-returning functions use RETURNS TABLE or RETURNS SETOF:

CREATE FUNCTION get_customer_orders(customer_id INT)
RETURNS TABLE(
    order_id INT,
    order_date TIMESTAMP,
    total NUMERIC
) AS $$
    SELECT id, created_at, total_amount
    FROM orders
    WHERE customer_id = get_customer_orders.customer_id
    ORDER BY created_at DESC;
$$ LANGUAGE SQL;

-- Use in queries like a table
SELECT * FROM get_customer_orders(42);

OUT parameters provide an alternative syntax for returning multiple values:

CREATE FUNCTION get_order_summary(
    IN order_id INT,
    OUT item_count INT,
    OUT subtotal NUMERIC,
    OUT tax NUMERIC,
    OUT total NUMERIC
) AS $$
BEGIN
    SELECT 
        COUNT(*),
        SUM(price * quantity),
        SUM(price * quantity) * 0.08,
        SUM(price * quantity) * 1.08
    INTO item_count, subtotal, tax, total
    FROM order_items
    WHERE order_id = get_order_summary.order_id;
END;
$$ LANGUAGE plpgsql;

-- Returns a single row with multiple columns
SELECT * FROM get_order_summary(123);

OUT parameters work well for returning a fixed set of calculated values. Use RETURNS TABLE when returning variable-length result sets.

Advanced Features

Exception handling in PL/pgSQL prevents errors from crashing your function:

CREATE FUNCTION safe_divide(numerator NUMERIC, denominator NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero attempted';
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Function volatility categories tell PostgreSQL how the function behaves:

• VOLATILE: Default. Function can modify database state or return different results with identical inputs
• STABLE: Returns consistent results within a single query for identical inputs but may vary across queries
• IMMUTABLE: Always returns identical results for identical inputs, never modifies database

Volatility matters for performance:

CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
IMMUTABLE AS $$
    SELECT amount * 0.08;
$$ LANGUAGE SQL;

-- PostgreSQL can use this in indexes
CREATE INDEX idx_price_with_tax 
ON products (calculate_tax(price));

Only mark functions IMMUTABLE if they truly never change behavior. Incorrectly marking a function IMMUTABLE when it queries tables or uses non-deterministic operations causes subtle bugs.

SECURITY DEFINER runs functions with the privileges of the user who created them, not the user calling them:

CREATE FUNCTION secure_salary_update(emp_id INT, new_salary NUMERIC)
RETURNS VOID
SECURITY DEFINER AS $$
BEGIN
    UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

-- Grant execute to users without granting direct UPDATE privileges
GRANT EXECUTE ON FUNCTION secure_salary_update TO hr_staff;

Managing and Debugging Functions

Use CREATE OR REPLACE FUNCTION to modify existing functions without dropping them:

CREATE OR REPLACE FUNCTION calculate_discount(
    original_price NUMERIC,
    discount_percent NUMERIC
) RETURNS NUMERIC AS $$
    SELECT original_price * (1 - discount_percent / 100.0);
$$ LANGUAGE SQL IMMUTABLE;

Debug functions with RAISE NOTICE:

CREATE FUNCTION debug_order_total(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    subtotal NUMERIC;
    total NUMERIC;
BEGIN
    SELECT SUM(price * quantity) INTO subtotal
    FROM order_items
    WHERE order_id = debug_order_total.order_id;
    
    RAISE NOTICE 'Subtotal: %', subtotal;
    
    total := subtotal * 1.08;
    RAISE NOTICE 'Total with tax: %', total;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Query system catalogs to inspect functions:

-- List all custom functions
SELECT 
    n.nspname AS schema,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema, function_name;

Drop functions with DROP FUNCTION:

DROP FUNCTION IF EXISTS calculate_discount(NUMERIC, NUMERIC);

Best Practices and Common Pitfalls

Always specify parameter types in DROP statements—PostgreSQL allows function overloading, so calculate_discount with different parameter types are different functions.

Name functions with verb prefixes: calculate_, get_, validate_. This distinguishes them from tables and makes their purpose clear.

Avoid functions for simple operations. A function that wraps SELECT * FROM users WHERE id = $1 adds overhead without benefit. Use functions when they provide genuine value through logic encapsulation or performance improvement.

Don’t perform external API calls or file operations in functions. These operations block, aren’t transactional, and create dependencies that complicate testing and deployment.

Test function performance under realistic data volumes. A function that performs well with 100 rows might crawl with 100,000. Use EXPLAIN ANALYZE to understand execution plans.

Version control your functions. Store them in migration files alongside schema changes. This makes deployments repeatable and provides change history.

Remember that functions execute in transactions. If a function modifies data and the calling transaction rolls back, those modifications roll back too. This is usually what you want, but can surprise developers expecting functions to behave like independent operations.

PostgreSQL functions are powerful tools when used appropriately. They reduce network overhead, centralize logic, and enable optimizations impossible in application code. Master the basics, understand volatility categories, and apply them where they provide clear benefits.