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: They reduce round tripsEvery time your backend sends a query to the database, there is network overhead. If an operation requires five queries, that is five round trips. A well-designed database function can perform all five steps inside the database and expose them through a single call from your backend.
They keep logic close to the dataMany operations are purely about data transformation or validation: calculating totals, applying discounts, enforcing constraints, or normalizing formats. When this logic runs inside the database, the data does not need to move across the network, which can improve performance and reduce complexity in your application layer.
They promote reusabilityOnce a function is defined, any client that can connect to your database can use it: backend services, scripts, scheduled jobs, and reporting tools. This eliminates duplicated logic across multiple codebases and helps keep business rules consistent.
They simplify application codeInstead of embedding complex SQL directly in your backend, you can expose clear, well-named functions with well-defined parameters. The application code focuses on orchestration and domain logic, while the database takes care of data-specific operations. They reduce round tripsEvery time your backend sends a query to the database, there is network overhead. If an operation requires five queries, that is five round trips. A well-designed database function can perform all five steps inside the database and expose them through a single call from your backend. They keep logic close to the dataMany operations are purely about data transformation or validation: calculating totals, applying discounts, enforcing constraints, or normalizing formats. When this logic runs inside the database, the data does not need to move across the network, which can improve performance and reduce complexity in your application layer. They promote reusabilityOnce a function is defined, any client that can connect to your database can use it: backend services, scripts, scheduled jobs, and reporting tools. This eliminates duplicated logic across multiple codebases and helps keep business rules consistent. They simplify application codeInstead of embedding complex SQL directly in your backend, you can expose clear, well-named functions with well-defined parameters. The application code focuses on orchestration and domain logic, while the database takes care of data-specific operations. 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); 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; 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) SELECT calculate_discounted_price(1, 10);  
-- Returns 1080.00 (10% off 1200) What’s happening here: CREATE OR REPLACE FUNCTION defines a new function or updates an existing one.
The function accepts product_id and discount_percent as inputs.
RETURNS NUMERIC specifies that the function returns a numeric value.
The DECLARE block defines local variables used inside the function.
The BEGIN ... END block contains the actual logic:

It loads the product price into original_price.
It validates the input and raises an exception if the product does not exist.
It computes the discounted price and returns it.


The $$ ... $$ construct (dollar-quoting) marks the start and end of the function body. CREATE OR REPLACE FUNCTION defines a new function or updates an existing one. CREATE OR REPLACE FUNCTION The function accepts product_id and discount_percent as inputs. product_id discount_percent RETURNS NUMERIC specifies that the function returns a numeric value. RETURNS NUMERIC The DECLARE block defines local variables used inside the function. DECLARE The BEGIN ... END block contains the actual logic:

It loads the product price into original_price.
It validates the input and raises an exception if the product does not exist.
It computes the discounted price and returns it. BEGIN ... END It loads the product price into original_price.
It validates the input and raises an exception if the product does not exist.
It computes the discounted price and returns it. It loads the product price into original_price. original_price It validates the input and raises an exception if the product does not exist. It computes the discounted price and returns it. The $$ ... $$ construct (dollar-quoting) marks the start and end of the function body. $$ ... $$ 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; 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: RETURNS VOID SELECT log_price_change(1, 1200.00, 1080.00); 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. RETURNS TABLE 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; 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); 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. RETURN QUERY 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; 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 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; 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'); SELECT signup_user('user@example.com', 'New User'); This approach has several benefits: Three inserts are wrapped in a single, well-defined function.
Only one round trip is required between your application and the database.
The workflow is centralized in one place, making it easier to maintain and reason about. Three inserts are wrapped in a single, well-defined function. Only one round trip is required between your application and the database. The workflow is centralized in one place, making it easier to maintain and reason about. 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. Forgetting error handlingAlways validate assumptions inside your function. Check forNULL values or missing records and use RAISE EXCEPTION to produce clear, actionable errors. This helps you catch issues early and avoid silent failures.
Writing overly large functionsIf a function is responsible for many unrelated concerns, it becomes difficult to read, test, and maintain. Prefer smaller, focused functions that do one thing well. You can compose these functions from your application code or from other SQL functions.
Not testing functions directlyBefore integrating a function into your backend, test it frompsql or your preferred SQL client. Verify inputs, outputs, error behavior, and edge cases. Treat database functions as first-class units of logic, not as hidden implementation details.
Ignoring permissionsBy default, functions run with the privileges of the caller. If the database user used by your application cannot access a table referenced inside the function, calls will fail. In some cases, you can useSECURITY DEFINER to run a function with the privileges of the user who created it, but this must be used carefully to avoid security risks. Forgetting error handlingAlways validate assumptions inside your function. Check forNULL values or missing records and use RAISE EXCEPTION to produce clear, actionable errors. This helps you catch issues early and avoid silent failures. NULL RAISE EXCEPTION Writing overly large functionsIf a function is responsible for many unrelated concerns, it becomes difficult to read, test, and maintain. Prefer smaller, focused functions that do one thing well. You can compose these functions from your application code or from other SQL functions. Not testing functions directlyBefore integrating a function into your backend, test it frompsql or your preferred SQL client. Verify inputs, outputs, error behavior, and edge cases. Treat database functions as first-class units of logic, not as hidden implementation details. psql Ignoring permissionsBy default, functions run with the privileges of the caller. If the database user used by your application cannot access a table referenced inside the function, calls will fail. In some cases, you can useSECURITY DEFINER to run a function with the privileges of the user who created it, but this must be used carefully to avoid security risks. SECURITY DEFINER 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. CREATE OR REPLACE FUNCTION To delete a function entirely: To delete a function entirely: DROP FUNCTION IF EXISTS calculate_discounted_price(INT, NUMERIC); 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 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: The logic is primarily about data (calculations, transformations, validations).
You want to reduce network round trips by grouping related queries.
Multiple applications or services need to share the same behavior.
You want to enforce certain business rules at the database level. The logic is primarily about data (calculations, transformations, validations). You want to reduce network round trips by grouping related queries. Multiple applications or services need to share the same behavior. You want to enforce certain business rules at the database level. Avoid or limit database functions when: The logic depends on external services (HTTP APIs, file systems, queues).
The function would become extremely complex and difficult to debug.
The team is not ready to maintain logic split between application code and SQL. The logic depends on external services (HTTP APIs, file systems, queues). The function would become extremely complex and difficult to debug. The team is not ready to maintain logic split between application code and SQL. 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: Wrap a frequently used query in a function.
Extract repeated data transformations into reusable functions.
Gradually introduce functions for workflows that currently require multiple round trips. Wrap a frequently used query in a function. Extract repeated data transformations into reusable functions. Gradually introduce functions for workflows that currently require multiple round trips. 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.

Optimism

Mastering Database Functions: A Beginner's Guide to Simplifying Backend Code

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

A Practical Guide to Table Partitioning in PostgreSQL

10 Ways Stand Out as a Java Developer and Land that Dream Job

10 Fantastic JavaScript Projects for Beginners

11 Great Tips From A Guy Who Leveled Up From Intern To Dev

125 Stories To Learn How To Do X

3 Free Python Courses For Beginners: 2020 Edition

A Practical Guide to Table Partitioning in PostgreSQL

10 Ways Stand Out as a Java Developer and Land that Dream Job

10 Fantastic JavaScript Projects for Beginners

11 Great Tips From A Guy Who Leveled Up From Intern To Dev

125 Stories To Learn How To Do X

3 Free Python Courses For Beginners: 2020 Edition

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps