Introduction to Stored Procedures: Automating Logic in PostgreSQL

Previously in this course, we explored Database Constraints Refinement: Multi-Column Logic in PostgreSQL to ensure data integrity at the row level. While constraints act as the database's "safety guardrails," sometimes you need to execute multi-step operations or complex business processes that require more than a single SQL statement.

This lesson introduces stored procedures, which allow you to encapsulate reusable business logic directly within your database using PL/pgSQL.

What are Stored Procedures?

A stored procedure is a block of code stored in the database that can be executed on demand. Unlike standard SQL queries that you send from your application code, a stored procedure lives inside the database server.

Think of them as "server-side scripts." They are useful for:

• Encapsulating complexity: Grouping multiple SQL statements into a single, atomic call.
• Performance: Reducing "chattiness" between your application and the database.
• Security: Granting users the right to execute a procedure without giving them direct UPDATE or DELETE access to the underlying tables.

Getting Started with PL/pgSQL

PL/pgSQL is PostgreSQL's procedural language. It allows you to use variables, loops, and conditional logic (similar to what you might have seen in Introduction to Conditionals: Controlling Logic in JavaScript).

To define a procedure, we use the CREATE PROCEDURE command.

Worked Example: Applying a Seasonal Discount

In our store project, imagine we want to apply a 10% discount to all products in a specific category. Instead of writing two separate update queries, we can create a procedure.

SQL
CREATE OR REPLACE PROCEDURE apply_category_discount(
    category_name TEXT,
    discount_rate NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET price = price * (1 - discount_rate)
    WHERE category = category_name;
END;
$$;

Here is the breakdown of the syntax:

1. CREATE OR REPLACE PROCEDURE: Defines the procedure name and its input parameters.
2. LANGUAGE plpgsql: Tells PostgreSQL we are using the procedural language.
3. AS $$ ... $$: The "dollar quoting" delimiters. Everything inside is the code body.
4. BEGIN ... END: The block that contains your SQL logic.

Calling the Procedure

Once created, you don't use SELECT to run a procedure. Instead, you use the CALL command.

SQL
-- Apply a 10% discount (0.10) to the 'Electronics' category
CALL apply_category_discount('Electronics', 0.10);

Hands-on Exercise

For your store project, create a procedure called log_transaction_status (or similar) that updates the status of an order.

1. Assume you have an orders table. Create a procedure that takes an order_id (integer) and a new_status (text) as parameters.
2. The procedure should perform an UPDATE on the orders table to set the status column where the id matches the input order_id.
3. Test your procedure by calling it with a valid order_id and a status like 'SHIPPED'.

Common Pitfalls

• Forgetting the CALL keyword: Newcomers often try to run procedures with SELECT, which is reserved for functions. Always use CALL for procedures.
• Transaction Scope: Procedures in PostgreSQL support transactions. If you use COMMIT inside a procedure, it saves all changes made up to that point. Be careful with manual transaction control; it's often better to let the calling application manage the transaction boundary.
• Over-complicating: If a task can be done with a single UPDATE or INSERT statement, don't wrap it in a procedure. Only use them when you need to group multiple actions or hide complex logic.

FAQ

Q: What is the difference between a Function and a Procedure? A: Functions must return a value and are usually used in SELECT queries. Procedures do not have to return a value and are intended for executing actions (side effects).

Q: Can I use SELECT statements inside a procedure? A: Yes, but you must store the results in a variable using SELECT ... INTO.

Q: Are stored procedures better than application-side logic? A: It depends. Database-side logic is faster for data-heavy operations but can make versioning and testing more difficult compared to application code.

Recap

In this lesson, we learned that stored procedures are powerful tools for encapsulating logic within the database. We used PL/pgSQL to define a procedure with parameters, learned how to execute it with the CALL command, and discussed the importance of keeping database logic clean and focused. By centralizing these operations, you ensure that your store application's business rules remain consistent, regardless of which language or service accesses the database.

Up next: We will explore Creating Triggers, which allow you to automatically fire these procedures when specific database events occur.