Stored Procedures in PostgreSQL – When, Why & How to Use Them (From Noob to Pro)

Hey, I’m Gaurav.

When I first heard about Stored Procedures, I thought they were just “fancy functions” that old developers used.

I avoided them for a long time because they looked complicated and I believed “everything should be done in application code”.

But during real projects and interviews, I realized Stored Procedures are extremely powerful when used correctly.

Today, I’ll explain Stored Procedures from the very basics — how I went from avoiding them to confidently using them.

We’ll cover:

- What is a Stored Procedure?

- Why & When to use them

- How to create them

- Pros & Cons

- Best practices

All examples use PostgreSQL and our familiar `users` and `orders` tables. You can practice on [sqlpractice.in](https://sqlpractice.in) (note: some advanced stored procedure features work best in PostgreSQL).

---

What is a Stored Procedure?

A Stored Procedure is a saved block of SQL code (with logic, variables, loops, conditions, and transactions) that is stored in the database and can be executed by name.

Unlike a simple View or Function, a Stored Procedure can:

- Perform multiple operations (INSERT, UPDATE, DELETE)

- Contain business logic

- Return multiple result sets

- Handle exceptions

- Run as a single transaction

Basic Syntax in PostgreSQL (using PL/pgSQL):

```sql

CREATE OR REPLACE PROCEDURE procedure_name(param1 datatype, param2 datatype)

LANGUAGE plpgsql

AS $$

DECLARE

-- variables

BEGIN

-- SQL statements + logic

COMMIT; -- or ROLLBACK on error

END;

$$;

```

---

Example 1: Simple Stored Procedure

Question: Create a procedure that registers a new order and updates product stock in one go.

Noob approach: Do both operations separately from the application (risk of partial failure).

Better approach (using Stored Procedure):

```sql

CREATE OR REPLACE PROCEDURE place_order(

p_user_id BIGINT,

p_product_id BIGINT,

p_quantity INT,

p_total_price DECIMAL

)

LANGUAGE plpgsql

AS $$

BEGIN

-- Start transaction

INSERT INTO orders (user_id, product_id, quantity, total_price, ordered_at)

VALUES (p_user_id, p_product_id, p_quantity, p_total_price, NOW());

-- Update stock

UPDATE products

SET stock = stock - p_quantity

WHERE id = p_product_id;

-- If stock goes negative, rollback

IF (SELECT stock FROM products WHERE id = p_product_id) < 0 THEN

RAISE EXCEPTION 'Insufficient stock for product %', p_product_id;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

RAISE;

END;

$$;

```

How to call it:

```sql

CALL place_order(101, 5, 2, 1999.99);

```

Practice Question 1 (Easy)

Create a simple stored procedure that inserts a new user.

[Try it live →](https://sqlpractice.in/?question=stored-procedure-basic)

---

Example 2: Complex Business Logic

Question: Create a procedure to process monthly salary credit with proper validation and logging.

Key Benefits Here:

- All logic stays in database (consistent)

- Atomic operation (all or nothing)

- Better performance (less network round trips)

---

When Should You Use Stored Procedures? (My Decision Framework)

Use Stored Procedures when:

- You need multiple SQL statements as one atomic unit

- Business logic must be consistent across all applications

- You want to reduce network round trips (good for performance)

- Complex validation or calculations are needed

- You need fine-grained error handling and transactions

- Security is critical (execute with limited privileges)

Avoid Stored Procedures when:

- Logic is simple and changes frequently (better in application code)

- You need heavy computation (Python/Node.js is better)

- Your team is not comfortable with PL/pgSQL

- You want full version control of logic in Git

Golden Rule I follow:

- Data logic + validation → Stored Procedure

- Business rules + UI logic → Application layer

---

Pros and Cons

Advantages:

- Better performance (compiled once, executed many times)

- Reduced network traffic

- Stronger security (users can execute procedure without direct table access)

- Atomic transactions across multiple statements

- Centralized business logic

Disadvantages:

- Harder to version control (logic lives in DB)

- Debugging is more difficult than application code

- Vendor lock-in (PL/pgSQL is PostgreSQL-specific)

- Can become a "black box" if overused

---

Best Practices for Stored Procedures

1. Keep them small and focused — One responsibility per procedure

2. Always use proper error handling (`EXCEPTION` block)

3. Use meaningful parameter names (`p_user_id` instead of `id`)

4. Document with comments inside the procedure

5. Test thoroughly — especially edge cases and rollback scenarios

6. Grant execute permission only — never give direct table access if possible

---

Final Thoughts

I used to think Stored Procedures were outdated.

Now I see them as a powerful tool for the right situations — especially when data integrity, performance, and consistency matter more than flexibility.

The key mindset shift was:

> “Not everything needs to be in the application layer. Some critical data operations belong in the database.”

Start by creating simple stored procedures for common operations like placing orders, updating profiles, or processing payments.

Go practice creating and calling Stored Procedures on [sqlpractice.in](https://sqlpractice.in). Even if the playground has limitations, try writing them.

You’ll quickly understand when they make your system more robust.

Keep learning — you're building strong backend/database thinking! 💪

Building sqlpractice.in to help everyone master SQL for interviews and real projects — completely free.