PL/pgSQL Stored Procedures

You've mastered queries, indexes, and transactions, but every time your application needs to perform a multi-step database operation, you find yourself writing the same verbose SQL over and over in application code. That repetition is a maintenance trap: business logic scattered across services, performance suffering from round trips, and security risks from ad-hoc SQL. The solution is to move that logic into the database itself with stored procedures. In this lesson, you'll learn to write stored procedures with PL/pgSQL, PostgreSQL's built-in procedural language, turning complex operations into a single, reusable call.

The problem this lesson solves

Imagine you run an e-commerce platform. Every order placement involves several steps: check stock, decrement inventory, create the order record, and update the customer's balance. In a typical application, you might execute four or five separate SQL statements, each requiring a network round trip. If a step fails midway, you're left with inconsistent data — inventory decremented but no order created. Writing this logic in application code also means duplicating it in every service that needs to place an order, making it brittle and hard to maintain. Stored procedures solve this by keeping the entire sequence inside the database, where it can be transactional, consistent, and reused by any client that needs it.

Core concept / mental model

Think of a stored procedure as a named, server-side recipe that the database executes for you. Instead of sending individual ingredients (SQL statements) and asking the chef (database) to prepare each step separately, you hand him the whole recipe card at once. The chef knows all the steps, can adjust quantities, and can stop the process if something goes wrong — without you having to watch over his shoulder. In PostgreSQL, this recipe card is written in PL/pgSQL, a procedural language that extends SQL with variables, loops, conditionals, and error handling. It's like a scripting language for your database, but one that is deeply integrated with SQL syntax.

Key components

• CREATE PROCEDURE: The command to define a new stored procedure.
• Parameter modes: IN (input), OUT (output), and INOUT (both) to pass data in and out.
• PL/pgSQL blocks: The body of the procedure, enclosed in $$ ... $$, containing declarations and statements.
• CALL: The command to invoke a stored procedure from SQL or another procedural context.

How it works step by step

Let's walk through the lifecycle of a stored procedure. This is the logical sequence from defining to calling it.

1. Define the procedure using CREATE PROCEDURE. You specify the name, any parameters, and the PL/pgSQL body that contains the logic.
2. Write the PL/pgSQL block inside the body. This block can declare variables, execute SQL statements, use conditionals (IF), loops (FOR), and raise exceptions.
3. Compile and store — PostgreSQL parses and stores the procedure in the database catalog. No execution happens yet.
4. Call the procedure using CALL procedure_name(parameters). The database executes the body in a single transaction context, unless you explicitly manage transactions.
5. Handle results — procedures can return values via OUT parameters or RETURNS TABLE, making them usable in queries.

Parameter modes in detail

• IN: Passes a value into the procedure. This is the default.
• OUT: Returns a value to the caller. Procedures with OUT parameters can return multiple values, but they cannot be used in SELECT like functions — you use CALL instead.
• INOUT: Passes a value in, modifies it, and returns it.

Hands-on walkthrough

Let's build a real example: a stored procedure that places an order and updates inventory. We'll start with the table setup, then the procedure, and finally call it.

Step 1: Set up sample tables

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock INTEGER NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

INSERT INTO products (name, stock) VALUES ('Laptop', 10), ('Mouse', 50);

Step 2: Create a stored procedure

This procedure takes a product ID and quantity, checks stock, updates it, and inserts an order.

CREATE OR REPLACE PROCEDURE place_order(
    p_product_id INTEGER,
    p_quantity INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock INTEGER;
BEGIN
    -- Read current stock
    SELECT stock INTO v_stock FROM products WHERE id = p_product_id FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % not found', p_product_id;
    END IF;

    IF v_stock < p_quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product %: available %, requested %',
            p_product_id, v_stock, p_quantity;
    END IF;

    -- Update stock
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

    -- Insert order
    INSERT INTO orders (product_id, quantity) VALUES (p_product_id, p_quantity);

    COMMIT; -- not recommended inside procedures; see notes below
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

Step 3: Call the procedure

CALL place_order(1, 2);
CALL place_order(2, 100); -- should raise an error

Expected output for the first call:

CALL

The second call will fail with:

ERROR:  Insufficient stock for product 2: available 50, requested 100
CONTEXT:  PL/pgSQL function place_order(integer,integer) line 9 at RAISE

Step 4: Verify results

SELECT * FROM products ORDER BY id;
SELECT * FROM orders;

You'll see product 1 stock now 8 and one order row.

Pro tip: In PostgreSQL, procedures (unlike functions) can manage transactions with COMMIT and ROLLBACK. However, doing so inside a procedure can break atomicity if called from a larger transaction. In most cases, you should let the caller control transactions. In the example above, we used COMMIT for demonstration, but in practice, avoid COMMIT/ROLLBACK inside procedures unless you truly need autonomous transaction control.

A procedure with OUT parameters

CREATE OR REPLACE PROCEDURE get_product_stats(
    p_product_id INTEGER,
    OUT total_stock INTEGER,
    OUT total_orders INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT stock INTO total_stock FROM products WHERE id = p_product_id;
    SELECT COUNT(*) INTO total_orders FROM orders WHERE product_id = p_product_id;
END;
$$;

CALL get_product_stats(1, NULL, NULL);

Expected output:

 total_stock | total_orders
-------------+--------------
           8 |            1

Note on calling procedures with OUT parameters: You must pass as many NULL placeholders as the number of OUT parameters. In PostgreSQL, OUT parameters are not explicitly marked in the CALL syntax.

Compare options / when to choose what

Stored procedures vs. functions vs. application code — which should you use? The choice depends on your needs. Here's a quick comparison.

When to choose stored procedures: - When you need to ensure atomicity across multiple statements without application coordination. - When you want to reduce network overhead by executing logic close to the data. - When you need to enforce data integrity and security at the database level.

When to choose functions: - When you need to return a set of rows that can be directly queried (e.g., SELECT * FROM my_function()). - When you want to embed logic inside a view or a query.

When to stick with application code: - When your logic is highly dynamic or needs external data (web APIs, message queues). - When you need cross-database portability and your team is not comfortable with SQL.

Variations: Instead of PL/pgSQL, PostgreSQL also supports other procedural languages like PL/Python, PL/Perl, and PL/Tcl, and you can use SQL-language functions for simple cases. Choose PL/pgSQL for its tight integration and performance.

Troubleshooting & edge cases

Stored procedures come with their own snags. Here's what we've seen and how to fix them.

"ERROR: procedure does not exist"

• Cause: You called it with the wrong name or wrong number of arguments, or the procedure is in a different schema.
• Fix: Verify with \df in psql, and qualify with schema: CALL myschema.place_order(...).

"Cannot begin/end transactions in PL/pgSQL"

• Cause: You tried to use BEGIN/COMMIT inside a function (which doesn't allow transaction control) or inside a procedure but in a context that doesn't permit it (e.g., called within an explicit transaction block).
• Fix: Use procedures for transaction control, and avoid explicit BEGIN/COMMIT unless necessary. If you must, call the procedure outside a larger transaction block.

"Variable not found" errors

• Cause: You used a variable name that doesn't exist, or you used a column alias that conflicts with a variable.
• Fix: Use explicit prefixes like v_ for variables to avoid ambiguity. Also, ensure INTO statements use the correct variable name.

Performance pitfalls

• Row-by-row processing inside loops can be slow. Prefer set-based SQL operations whenever possible.
• Lock contention if you use FOR UPDATE but don't commit soon. Keep transactions short.
• No index on columns used in the procedure's WHERE clauses. Make sure to index those.

Edge case: procedures with no parameters

You can create a procedure with no parameters, but you must still call it with an empty argument list: CALL my_proc(). Note that the parentheses are optional if there are no parameters, but it's safer to include them.

What you learned & what's next

You've now mastered the essentials of writing stored procedures with PL/pgSQL. Let's recap what you learned:

• The core idea: Stored procedures encapsulate multi-step SQL logic inside the database, reducing round trips and enforcing consistency.
• The syntax: CREATE PROCEDURE with parameter modes (IN, OUT, INOUT) and the PL/pgSQL block structure with DECLARE, BEGIN, EXCEPTION.
• How to call: Use CALL procedure_name(args) and handle OUT parameters properly.
• Trade-offs: Procedures vs. functions vs. application code — when each shines.
• Troubleshooting: Common errors like missing procedure, transaction issues, and performance tips.

You've now built a reusable order-placement procedure. In the next lesson, we'll explore user-defined functions and how they differ from procedures, allowing you to choose the right tool for every scenario.

Next step: Try writing a procedure that updates customer balances when an order is placed — think about adding a customer_id parameter and an UPDATE inside the same transaction block.