For a long time, PostgreSQL only had functions — no true stored procedures — which meant every piece of reusable server-side logic had to work within the constraints of a single implicit transaction. That changed with PostgreSQL 11, which introduced CREATE PROCEDURE as a genuinely distinct object type, with the ability to manage its own transactions using COMMIT and ROLLBACK inside the procedure body. If I need to run a long multi-step batch job — processing thousands of records with periodic commits along the way — stored procedures are the right tool, in a way that functions simply can’t replicate.
In this guide, I’ll explain what stored procedures are, how they differ from functions, the syntax for creating and calling them, practical real-world examples, transaction control specifics, troubleshooting tips, and the best practices I follow when writing them.
What Is a Stored Procedure?
A stored procedure is a named, reusable block of procedural code stored in the database, invoked with the CALL statement rather than being used inline in a SELECT. The defining difference between a procedure and a function is transaction control: a procedure can issue COMMIT and ROLLBACK statements internally, which lets me break a large batch of work into multiple committed chunks, rather than being bound to the single transaction of whatever statement invoked it.
Functions, by contrast, always execute within the transaction of the calling statement and cannot commit or roll back independently.
Stored Procedures vs. Functions: Key Differences
| Aspect | Function | Procedure |
|---|---|---|
| Invocation | SELECT my_function() | CALL my_procedure() |
| Return value | Required (or void) | No return value (though OUT parameters are supported) |
| Transaction control | Cannot COMMIT/ROLLBACK internally | Can COMMIT/ROLLBACK internally |
| Usable inside a SELECT/query expression | Yes | No |
| Typical use case | Computing and returning a value | Multi-step operations, batch processing |
Basic Syntax
CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)
LANGUAGE plpgsql
AS $$
BEGIN
-- procedure body
END;
$$;
I call it with:
CALL procedure_name(arguments);
Example 1: A Simple Procedure Without Transaction Control
CREATE OR REPLACE PROCEDURE deactivate_user(p_user_id INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users SET is_active = false WHERE id = p_user_id;
INSERT INTO user_activity_log (user_id, action, logged_at)
VALUES (p_user_id, 'deactivated', now());
END;
$$;
CALL deactivate_user(42);
Both statements inside this procedure run within the same transaction as the CALL statement itself, exactly like they would inside a function — the real power of procedures shows up once I add explicit transaction control.
Example 2: Batch Processing with Periodic Commits
This is the classic use case that justifies procedures over functions. Suppose I need to update millions of rows in a large table, and I want to commit in batches to avoid holding one enormous, long-running transaction (which can bloat WAL, hold locks longer than necessary, and make the operation harder to recover from if interrupted):
CREATE OR REPLACE PROCEDURE recalculate_loyalty_points(batch_size INTEGER DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
rows_updated INTEGER;
BEGIN
LOOP
UPDATE customers
SET loyalty_points = loyalty_points + (total_spent * 0.01)::INTEGER
WHERE id IN (
SELECT id FROM customers
WHERE loyalty_recalculated = false
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
UPDATE customers SET loyalty_recalculated = true
WHERE id IN (
SELECT id FROM customers
WHERE loyalty_recalculated = false
LIMIT batch_size
);
COMMIT;
EXIT WHEN rows_updated = 0;
END LOOP;
END;
$$;
CALL recalculate_loyalty_points(5000);
Each iteration of the loop commits its own batch, meaning if the process is interrupted partway through — a server restart, a connection drop, or a manual cancellation — the already-committed batches stay committed rather than being rolled back entirely. This kind of resumable, incremental batch processing simply isn’t possible inside a plain function.
Example 3: Using ROLLBACK Inside a Procedure
CREATE OR REPLACE PROCEDURE process_orders_with_validation()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id, amount FROM pending_orders LOOP
BEGIN
IF r.amount <= 0 THEN
RAISE EXCEPTION 'Invalid amount for order %', r.id;
END IF;
UPDATE pending_orders SET status = 'processed' WHERE id = r.id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
UPDATE pending_orders SET status = 'failed', error_message = SQLERRM WHERE id = r.id;
COMMIT;
END;
END LOOP;
END;
$$;
This pattern — commit on success, rollback and log on failure, per individual order — lets me process a batch where a single bad record doesn’t block or corrupt the processing of every other record in the batch.
Procedures with IN, OUT, and INOUT Parameters
CREATE OR REPLACE PROCEDURE transfer_funds(
IN from_account INTEGER,
IN to_account INTEGER,
IN amount NUMERIC,
OUT success BOOLEAN,
OUT message TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
from_balance NUMERIC;
BEGIN
SELECT balance INTO from_balance FROM accounts WHERE id = from_account FOR UPDATE;
IF from_balance < amount THEN
success := false;
message := 'Insufficient funds';
RETURN;
END IF;
UPDATE accounts SET balance = balance - amount WHERE id = from_account;
UPDATE accounts SET balance = balance + amount WHERE id = to_account;
success := true;
message := 'Transfer completed successfully';
END;
$$;
CALL transfer_funds(101, 202, 500.00, NULL, NULL);
OUT parameters in a procedure return values back to the caller through the CALL statement’s result set, which is how I get output from a procedure without a formal RETURN value, since procedures otherwise don’t return anything.
Note that this particular example — a funds transfer — deliberately does not include an internal COMMIT, since I want the debit and credit to succeed or fail together as a single atomic unit within the caller’s transaction. This highlights an important point: just because procedures can manage transactions doesn’t mean every procedure should.
Calling One Procedure from Another
CREATE OR REPLACE PROCEDURE nightly_maintenance()
LANGUAGE plpgsql
AS $$
BEGIN
CALL recalculate_loyalty_points(5000);
CALL process_orders_with_validation();
COMMIT;
END;
$$;
Common Use Cases
- Batch data migrations or backfills that need periodic commits to avoid excessively long transactions.
- ETL-style processing — pulling from staging tables, transforming, and loading into production tables in controlled chunks.
- Multi-step business workflows that need mixed commit/rollback behavior at different points, like the order-processing example above.
- Scheduled maintenance tasks, often invoked via
pg_cronor an external scheduler, where the task needs to make incremental progress and survive partial failures gracefully. - Administrative operations — archiving old data, cleaning up expired records, or rebuilding derived/summary tables in batches.
Troubleshooting Common Issues
“invalid transaction termination” error This happens when I try to use COMMIT or ROLLBACK inside a function instead of a procedure, or inside a procedure that’s being called from within another transaction block that doesn’t allow it (for example, calling a procedure with internal COMMITs from inside another procedure that doesn’t expect that, or from a context where autocommit is off in a way that conflicts). I double check that COMMIT/ROLLBACK only appear inside genuine CREATE PROCEDURE bodies, never inside CREATE FUNCTION.
Procedure runs but changes don’t appear to persist If the calling client explicitly wraps the CALL statement inside its own BEGIN ... COMMIT block, internal commits inside the procedure can behave unexpectedly, since a procedure’s internal transaction control only works when it’s the outermost transaction context. I check whether my client library or ORM is silently wrapping every statement, including CALL, in an explicit transaction block.
Long-running batch procedure holds locks longer than expected Even with periodic commits, if a single iteration of a loop scans or locks a large number of rows before committing, lock contention can still build up. I tune the batch size down, and make sure WHERE clauses inside the loop are backed by proper indexes so each batch iteration is fast and holds locks for the shortest time possible.
GET DIAGNOSTICS returns unexpected row counts GET DIAGNOSTICS ... = ROW_COUNT reflects the row count of the most recently executed statement, so if I have multiple statements between the actual data-changing statement and the GET DIAGNOSTICS call, I get the wrong count. I place GET DIAGNOSTICS immediately after the statement whose row count I actually need.
Error handling swallowing genuinely unexpected exceptions A broad WHEN OTHERS exception handler inside a batch loop (as shown in the order-processing example) is great for keeping a batch job resilient to individual bad records, but it can also hide real bugs, like a typo in a column name that fails on every single row. I always log SQLERRM explicitly, as shown, and periodically review the failure log rather than assuming “no crash” means “everything worked.”
Best Practices
- Use procedures specifically when transaction control is genuinely needed. If a task can complete cleanly inside a single transaction, a function is usually simpler and works in more contexts (like inside a
SELECT). - Choose sensible batch sizes for commit-per-batch loops. Too small, and commit overhead dominates; too large, and I lose the benefit of shorter lock durations and resumability. I test and tune batch size against real data volumes.
- Always make batch procedures idempotent or resumable. If a procedure is interrupted partway through, I want to be able to safely re-run it without reprocessing (or double-processing) already-committed rows — the
loyalty_recalculatedflag pattern in the example above is a common way to achieve this. - Log progress and failures explicitly. For long-running procedures, I write progress or error information to a dedicated logging table so I can monitor status without needing to inspect the live data directly.
- Be explicit about which operations must be atomic. As shown in the funds-transfer example, not every procedure should commit internally — some operations genuinely need to succeed or fail as a single unit, and I make that decision deliberately rather than defaulting to “add commits everywhere.”
- Document expected call context. If a procedure assumes it’s the outermost transaction (because it manages its own commits), I document that clearly, since calling it from inside another already-open transaction can behave unexpectedly.
- Test failure scenarios, not just the happy path. I deliberately simulate bad data, connection drops mid-batch, and constraint violations to confirm the procedure behaves safely and predictably under real-world failure conditions.
Calling Procedures from Application Code
Most database drivers and ORMs distinguish between calling a function and calling a procedure, and I’ve run into subtle issues when application code treats them interchangeably. In raw psql or via most drivers, the call is straightforward:
CALL transfer_funds(101, 202, 500.00, NULL, NULL);
With psycopg2 in Python, for example, I use the cursor’s callproc() method or execute a CALL statement directly:
cur.execute("CALL transfer_funds(%s, %s, %s, NULL, NULL)", (101, 202, 500.00))
For procedures with OUT parameters, retrieving the result depends on the driver — most fetch the output values as if the CALL statement were a SELECT returning a single row, since that’s how PostgreSQL exposes OUT parameter results to the client.
Scheduling Procedures
Since procedures are the natural home for batch and maintenance logic, I frequently pair them with a scheduler. The pg_cron extension is the most common approach for in-database scheduling:
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule(
'nightly-maintenance',
'0 2 * * *',
'CALL nightly_maintenance()'
);
This runs the nightly_maintenance() procedure I defined earlier every night at 2 AM, entirely within the database, without needing an external cron job or application-level scheduler for this specific piece of maintenance work. I still use external orchestration tools (like Airflow or a simple systemd timer calling psql) for workflows that need to coordinate across multiple systems, but for purely database-internal maintenance, pg_cron combined with a well-structured procedure is a clean, low-overhead solution.
Dropping and Modifying Procedures
DROP PROCEDURE IF EXISTS deactivate_user(INTEGER);
CREATE OR REPLACE PROCEDURE works for most in-place changes to a procedure’s body, but changing parameter signatures (adding, removing, or reordering parameters) requires a full drop-and-recreate, the same as with functions.
Frequently Asked Questions
Can a procedure return a value like a function does? Not directly through RETURN with a value — procedures don’t have a return type. Output is communicated back to the caller exclusively through OUT (or INOUT) parameters, as shown in the funds-transfer example.
Can I call a procedure from inside a function? No — functions cannot execute CALL statements, since procedures may attempt transaction control that functions aren’t permitted to perform. Procedures can call other procedures, and both functions and procedures can call ordinary functions, but the function-calling-a-procedure direction is not supported.
What happens if an error occurs inside a procedure and it isn’t caught? An uncaught exception rolls back the current uncommitted work within that procedure’s active transaction segment, exactly like it would for any other statement, and the error propagates back to the calling client. Any batches from earlier iterations that were already explicitly committed inside the procedure remain committed, since PostgreSQL can’t roll back a transaction that’s already been finalized.
Is there a performance cost to using CALL versus a function invocation? The overhead is negligible for the procedure call mechanism itself; any performance difference in practice comes from the actual workload characteristics — particularly how transaction and commit boundaries are structured — rather than from CALL versus SELECT as an invocation mechanism.
Can procedures be used inside a trigger? No — trigger functions must be regular functions returning type trigger, not procedures, since triggers execute within the context of the statement that fired them and can’t independently manage transactions the way a top-level procedure call can.
Monitoring Long-Running Procedures
For batch procedures that run for an extended period, I keep visibility into progress by querying pg_stat_activity from another session while the procedure is running:
SELECT pid, query, state, now() - query_start AS running_time
FROM pg_stat_activity
WHERE query ILIKE 'CALL%'
ORDER BY running_time DESC;
Combined with a dedicated progress-logging table that the procedure itself updates periodically (for example, writing the current batch number and timestamp after each committed chunk), this gives me a reliable way to check on a long-running maintenance job without needing to guess whether it’s still making progress or has silently stalled.
When a Procedure Is Overkill
If a task fits comfortably inside a single transaction and doesn’t need internal commit/rollback control, I default to a plain function instead of a procedure — functions are usable inside SELECT statements, composable with other functions, and generally simpler for both the planner and future maintainers to reason about. I reach for a procedure specifically when the task’s nature genuinely demands multi-transaction behavior: batch jobs that need to survive interruption, or workflows with deliberately mixed commit and rollback semantics across different steps.
Final Thoughts
Stored procedures fill a real gap that functions can’t — genuine, self-managed transaction control for long-running or batch-oriented server-side logic. I reach for them specifically when a task needs to make durable, incremental progress: large data migrations, resilient batch processing, and scheduled maintenance jobs are the cases where procedures consistently prove their worth. For anything that fits neatly inside a single transaction and needs to return a computed value, I still default to a plain function — procedures are a deliberate tool for a specific job, not a blanket replacement.
