Skip to main content
PostgreSQL
CHAPTER 21 Intermediate

Stored Procedures and Functions in PostgreSQL

Updated: May 16, 2026
6 min read

# CHAPTER 21

Stored Procedures and Functions in PostgreSQL

1. Introduction

Until now, we have used PostgreSQL solely for data storage, relying on backend languages like PHP or Python to perform mathematical calculations and business logic. But what if you have a massive financial algorithm that is used by the PHP website, the iOS app, and the Android app? Instead of writing that logic three times in three different languages, you can write it *once* inside the database itself! In this chapter, we will learn how to extend PostgreSQL by writing custom Functions and Stored Procedures using its internal programming language: PL/pgSQL.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Understand the purpose of Database Encapsulation.
  • Create custom User-Defined Functions (UDFs).
  • Understand the structure of the PL/pgSQL language.
  • Pass parameters into functions.
  • Differentiate between a FUNCTION (which returns data) and a PROCEDURE (which performs actions and handles transactions).

3. What is PL/pgSQL?

Standard SQL is a "Declarative" language. You tell it *what* you want (e.g., SELECT names), but not *how* to get it. PL/pgSQL (Procedural Language/PostgreSQL) is an "Imperative" programming language built into Postgres. It allows you to write variables, IF/ELSE statements, and FOR loops directly inside the database.

4. Creating a Basic Function

A Function is designed to take inputs, perform logic, and return a value. Let's create a simple function to calculate a 10% tax on an order.
sql
123456789101112131415 
-- 1. Define the Function, the parameter, and the Return type
CREATE OR REPLACE FUNCTION calculate_tax(order_total NUMERIC) 
RETURNS NUMERIC 
LANGUAGE plpgsql
AS $$
-- 2. Define internal variables (optional)
DECLARE
    tax_rate NUMERIC := 0.10;
    tax_amount NUMERIC;
-- 3. Execute the logic
BEGIN
    tax_amount := order_total * tax_rate;
    RETURN tax_amount;
END;
$$;

*(Notice the $$ symbols. They act as quotes to encapsulate the entire block of code so PostgreSQL knows where the function begins and ends).*

5. Using Your Custom Function

Once created, your function acts exactly like the built-in functions (like SUM() or COUNT()). You can use it in any query!
sql
12 
SELECT product_name, price, calculate_tax(price) AS tax_owed 
FROM products;

6. Functions vs Stored Procedures

In PostgreSQL versions prior to version 11, developers used Functions for everything. However, Functions have a limitation: A Function cannot manage its own Transactions. You cannot write COMMIT or ROLLBACK inside a Function.

To solve this, PostgreSQL 11 introduced Stored Procedures.

  • Function (CREATE FUNCTION): Used to calculate and *return* data. Cannot manage transactions. Called using SELECT.
  • Procedure (CREATE PROCEDURE): Used to *execute* complex actions (like mass updates). Can run its own COMMIT and ROLLBACK logic! Does not return data. Called using CALL.

7. Creating a Stored Procedure

Let's build a massive banking procedure that transfers money between two users, complete with its own internal COMMIT logic!
sql
1234567891011121314 
CREATE OR REPLACE PROCEDURE transfer_funds(sender_id INT, receiver_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Deduct from sender
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    
    -- Add to receiver
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    
    -- The Procedure commits the transaction internally!
    COMMIT;
END;
$$;

8. Executing a Stored Procedure

You do not use SELECT for procedures. You use the CALL command.
sql
12 
-- Transfer $500 from Account 1 to Account 2
CALL transfer_funds(1, 2, 500.00);

9. Common Mistakes

  • Syntax Errors in PL/pgSQL: The PL/pgSQL language uses := for variable assignment, not =. (e.g., taxamount := 10;). Using = for assignment will cause a syntax error.
  • Forgetting LANGUAGE plpgsql: PostgreSQL supports many internal languages (you can even write functions in Python or C!). You MUST append LANGUAGE plpgsql (or LANGUAGE sql for simple queries) so the engine knows how to parse your code.

10. Best Practices

  • Don't Overuse Them: 20 years ago, companies put *all* their logic in Stored Procedures. Today, modern architectures (like microservices) prefer putting business logic in the backend code (Python/Node.js) because it is easier to track in version control (Git). Use Procedures sparingly, strictly for massive data operations that require maximum performance directly on the database server.

11. Exercises

  1. 1. Write a simple PL/pgSQL FUNCTION named getgreeting that takes no parameters and simply returns the VARCHAR string "Hello World".
  1. 2. How do you execute a PostgreSQL PROCEDURE?

12. SQL Challenges

Write a SQL query that uses your calculate_tax() function to find all products where the calculated tax is greater than $5.00.
sql
123 
SELECT product_name, price 
FROM products 
WHERE calculate_tax(price) > 5.00;

13. MCQ Quiz with Answers

Question 1

What is the defining architectural difference between a Function and a Stored Procedure in modern PostgreSQL?

Question 2

What keyword is used to execute a Stored Procedure in PostgreSQL?

14. Interview Questions

  • Q: Explain what PL/pgSQL is. Why would a Software Architect choose to encapsulate a complex billing algorithm inside a PostgreSQL Stored Procedure rather than writing it in the application's backend code?
  • Q: Describe the syntactic structure of a PL/pgSQL function (specifically the DECLARE and BEGIN/END blocks).

15. FAQs

Q: Can I return an entire table from a Function? A: Yes! PostgreSQL functions are incredibly powerful. You can define a function that RETURNS TABLE (col1 INT, col2 TEXT), allowing you to encapsulate complex JOIN logic and query it dynamically!

16. Summary

By mastering PL/pgSQL, you elevate your skills from a database user to a database programmer. Functions allow you to centralize repetitive math and string manipulations, while Stored Procedures empower you to encapsulate massive, multi-step transaction workflows directly adjacent to the metal of the hard drive, resulting in unparalleled performance.

17. Next Chapter Recommendation

Our functions are great, but a user still has to manually type CALL or SELECT to execute them. What if we want the database to execute a function *automatically*, like a tripwire, the exact millisecond a new row is inserted? In Chapter 22: Triggers and Event Automation, we will learn how to build event-driven database architectures.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Transactions and ACID Properties
Next Chapter Triggers and Event Automation

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6