Database Functions Changed How I Write Backend Code (A Beginner’s Guide)

TL;DR: Database functions let you move certain logic from your application code into the database itself. This guide explains what database functions are, why they matter for backend development, and how to write practical PostgreSQL functions you can start using immediately.

What Is a Database Function?

A database function is a named, reusable block of code that lives inside your database. You define it once and call it whenever you need it. Conceptually, it’s similar to a function in any programming language, but it executes inside the database engine.

In PostgreSQL, database functions are commonly written using PL/pgSQL. The syntax is different from languages like Go or Python, but the core idea remains the same: inputs go in, some logic runs, and a result comes out.

Why Database Functions Matter for Backend Developers

Database functions are not just a convenience feature. Used correctly, they can improve performance, consistency, and maintainability in your backend systems.

Here are key reasons they matter:

Your First Database Function

Consider a simple scenario: you have a products table and need to compute a discounted price for a given product.

First, define the table:

CREATE TABLE products (  
    id SERIAL PRIMARY KEY,  
    name TEXT NOT NULL,  
    price NUMERIC(10, 2) NOT NULL  
);  
  
INSERT INTO products (name, price) VALUES  
    ('Laptop', 1200.00),  
    ('Headphones', 85.00),  
    ('Keyboard', 45.00);

Now, create a function that takes a product ID and a discount percentage, then returns the discounted price:

CREATE OR REPLACE FUNCTION calculate_discounted_price(  
    product_id INT,  
    discount_percent NUMERIC  
)  
RETURNS NUMERIC AS $$  
DECLARE  
    original_price NUMERIC;  
    discounted_price NUMERIC;  
BEGIN  
    SELECT price INTO original_price  
    FROM products  
    WHERE id = product_id;  
  
    IF original_price IS NULL THEN  
        RAISE EXCEPTION 'Product with ID % not found', product_id;  
    END IF;  
  
    discounted_price := original_price - (original_price * discount_percent / 100);  
    RETURN discounted_price;  
END;  
$$ LANGUAGE plpgsql;

Usage:

SELECT calculate_discounted_price(1, 10);  
-- Returns 1080.00 (10% off 1200)

What’s happening here:

Functions That Return No Value

Not all functions need to return a value. Sometimes, the purpose is to perform an action such as logging or auditing.

Example: a function to log price changes:

CREATE OR REPLACE FUNCTION log_price_change(  
    product_id INT,  
    old_price NUMERIC,  
    new_price NUMERIC  
)  
RETURNS VOID AS $$  
BEGIN  
    INSERT INTO price_change_log (product_id, old_price, new_price, changed_at)  
    VALUES (product_id, old_price, new_price, NOW());  
END;  
$$ LANGUAGE plpgsql;

Here, RETURNS VOID indicates that the function does not return a value. You still call it like any other function:

SELECT log_price_change(1, 1200.00, 1080.00);

Functions That Return Multiple Rows

In many backend use cases, you want a function to return a set of rows—essentially behaving like a query with parameters. PostgreSQL supports this with RETURNS TABLE or RETURNS SETOF.

Example: get all products above a given price:

CREATE OR REPLACE FUNCTION get_expensive_products(min_price NUMERIC)  
RETURNS TABLE (  
    product_id INT,  
    product_name TEXT,  
    product_price NUMERIC  
) AS $$  
BEGIN  
    RETURN QUERY  
    SELECT id, name, price  
    FROM products  
    WHERE price >= min_price  
    ORDER BY price DESC;  
END;  
$$ LANGUAGE plpgsql;

Usage:

SELECT * FROM get_expensive_products(50.00);

This returns all products priced at or above 50. The RETURN QUERY statement runs the query and streams its results back to the caller.

Functions With Default Parameters

You can define default values for function parameters so callers don’t need to pass every argument explicitly.

Example: applying tax with a default rate:

CREATE OR REPLACE FUNCTION apply_tax(  
    amount NUMERIC,  
    tax_rate NUMERIC DEFAULT 7.5  
)  
RETURNS NUMERIC AS $$  
BEGIN  
    RETURN amount + (amount * tax_rate / 100);  
END;  
$$ LANGUAGE plpgsql;

Usage:

SELECT apply_tax(100);       -- Uses default 7.5% tax, returns 107.50  
SELECT apply_tax(100, 15);   -- Uses 15% tax, returns 115.00

This pattern is useful when you want a sane default but still allow explicit overrides.

A Practical Example: User Signup in One Call

Backend applications often need to perform multiple related database operations as part of a single workflow. User signup is a typical example: you might need to insert a user, create a default profile, and log the signup action.

All of this can be encapsulated in one function:

CREATE OR REPLACE FUNCTION signup_user(  
    user_email TEXT,  
    user_name TEXT  
)  
RETURNS INT AS $$  
DECLARE  
    new_user_id INT;  
BEGIN  
    -- Insert the user  
    INSERT INTO users (email, name, created_at)  
    VALUES (user_email, user_name, NOW())  
    RETURNING id INTO new_user_id;  
  
    -- Create default profile  
    INSERT INTO profiles (user_id, bio, avatar_url)  
    VALUES (new_user_id, '', '');  
  
    -- Log the signup  
    INSERT INTO activity_log (user_id, action, performed_at)  
    VALUES (new_user_id, 'signup', NOW());  
  
    RETURN new_user_id;  
END;  
$$ LANGUAGE plpgsql;

From your backend, you now call:

SELECT signup_user('user@example.com', 'New User');

This approach has several benefits:

Common Mistakes When Writing Database Functions

Beginners often run into the same issues when adopting database functions. Here are some of the most common and how to avoid them.

How to Update or Drop Functions

To change a function’s implementation, use CREATE OR REPLACE FUNCTION with the same name and parameter list. PostgreSQL will replace the existing definition.

To delete a function entirely:

DROP FUNCTION IF EXISTS calculate_discounted_price(INT, NUMERIC);

The parameter types are required because PostgreSQL supports function overloading: multiple functions can share the same name if they differ in parameter types or counts.

When to Use Database Functions—and When Not To

Database functions are powerful, but they are not the right tool for every scenario.

Use database functions when:

Avoid or limit database functions when:

The goal is not to move all logic into the database, but to move the right kind of logic closer to the data.

Conclusion

Database functions are a foundational tool for building robust backends on PostgreSQL. They help reduce network overhead, centralize data-specific logic, and keep your application code cleaner and more focused.

If you are new to database functions, start small:

Over time, you’ll develop an intuition for which responsibilities belong in your application layer and which are better implemented as database functions. That balance is where database functions really start to change how you design and write backend code.