Tired of Writing the Same SQL Queries Over and Over?

Imagine you’re stuck in a loop—executing the same SQL queries day in, day out. It feels a bit like Bill Murray in Groundhog Day, doesn’t it? Groundhog Day Gif You find yourself copying and pasting nearly identical code across different parts of your application or repeatedly typing out similar commands during database maintenance.

What if you could bundle these repetitive tasks into reusable, maintainable, and efficient code blocks inside your database? Good news: PostgreSQL makes this possible with Functions and Stored Procedures.

In this post, we’ll dive into what they are, how to use them, and why they’re essential for writing clean, scalable, and high-performing database systems.

1. PostgreSQL Functions vs. Stored Procedures: Understanding the Difference

PostgreSQL gives us two powerful tools to encapsulate logic:

  • Functions
  • Stored Procedures

Although they may seem similar at first glance, their purposes and behaviors differ in key ways.

PostgreSQL Functions

  • Must return a value.
  • Typically used for calculations, data lookups, and queries that produce a result.
  • Can be used within SELECT statements or called directly.

Example use cases:

  • Calculating employee bonuses.
  • Fetching aggregated sales data.
  • Returning a filtered list of records.

PostgreSQL Stored Procedures

  • Do not need to return a value.
  • Focused on performing operations, often involving multiple steps or transaction control.
  • Called using the CALL statement.

Example use cases:

  • Performing batch updates.
  • Managing complex business processes.
  • Inserting logs, archiving data, or chaining operations within a transaction.

Quick Comparison Table

Feature Function Stored Procedure
Return Type Must return a value (scalar or table) Optional return (can return nothing)
Execution Syntax SELECT function_name(params); CALL procedure_name(params);
Best For Computations, data retrieval Complex business logic, transaction flows
Transaction Control Cannot manage transactions Can start, commit, and roll back transactions

2. Writing Your First PostgreSQL Function

Let’s bring this to life with a practical example.

Imagine you have an employees table, and you frequently need to retrieve the salary of a particular employee based on their ID.

Instead of repeating the same query:

SELECT salary FROM employees WHERE id = 101;

You can create a PostgreSQL function to streamline this:

CREATE OR REPLACE FUNCTION get_employee_salary(emp_id INT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (
        SELECT salary
        FROM employees
        WHERE id = emp_id
    );
END;
$$ LANGUAGE plpgsql;

How to Call This Function:

SELECT get_employee_salary(101);

Benefits:

  • Less repetitive code.
  • Easy to maintain—if your salary calculation logic changes, you update it in one place.
  • Improved readability in your queries.

3. Building a PostgreSQL Stored Procedure

Let’s say you need to regularly give a 10% bonus to all employees in a specific department. This is a perfect job for a stored procedure, especially if you want to control transactions and possibly roll back in case of an error.

CREATE OR REPLACE PROCEDURE apply_department_bonus(dept_id INT, bonus_percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + (salary * bonus_percent / 100)
    WHERE department_id = dept_id;

    -- Optionally log this operation
    INSERT INTO bonus_log(department_id, bonus_given_on)
    VALUES (dept_id, CURRENT_TIMESTAMP);
END;
$$;

How to Call This Procedure:

CALL apply_department_bonus(3, 10);

Why Stored Procedures Shine Here:

  • You can wrap this inside a transaction block to commit or roll back as needed.
  • Multiple steps (update + logging) are handled cleanly.
  • Can be expanded to include complex control logic.

4. When to Use Functions vs. Stored Procedures

Use Case Recommended Tool
Calculations, single-result queries Function
CRUD operations with multiple steps Stored Procedure
Data transformation in SELECT queries Function
Workflow automation, logging, batch updates Stored Procedure
Requires transaction management Stored Procedure

5. Final Thoughts

Functions and stored procedures are more than just code-saving conveniences—they can significantly boost your PostgreSQL database’s efficiency, maintainability, and scalability.

  • Use functions when you need quick, reusable computations.
  • Use stored procedures when you need to orchestrate multi-step processes and manage transactions.

Start small. Wrap your most repetitive SQL queries in functions. When you’re ready to automate multi-step tasks, stored procedures will be your best friend.

If you’re serious about optimizing your PostgreSQL workflow, mastering these tools is an investment that will pay off in every project you touch.

Happy coding, and may your queries always be fast!