PostgreSQL stored procedures are named database objects that encapsulate procedural logic using PL/pgSQL or other procedural languages, allowing you to execute complex operations, manage transactions, and reuse code directly within the database. You define them with the CREATE PROCEDURE command, call them with CALL, and they can handle transaction control (COMMIT/ROLLBACK) that functions cannot. This article explains how stored procedures differ from functions, walks through a concrete example, and covers common pitfalls and best practices.
What Is a PostgreSQL Stored Procedure?
A stored procedure is a database object that groups one or more SQL statements and procedural commands (loops, conditionals, variable assignments) into a callable unit. Unlike a regular SQL query that returns a result set, a procedure is primarily executed for its side effects—modifying data, performing administrative tasks, or orchestrating multiple steps. PostgreSQL introduced true stored procedures in version 11 (2018); before that, only functions were available, which had limitations with transaction control.
Key characteristics of a stored procedure:
- Created with
CREATE PROCEDUREand invoked withCALL. - Can manage transactions using
COMMITandROLLBACKinside the procedure body. - Cannot be used in a SELECT statement; it does not return a value like a function.
- Supports multiple procedural languages: PL/pgSQL (default), SQL, PL/Python, PL/Perl, and others.
- Can have input (
IN), output (OUT), and input-output (INOUT) parameters.
Stored Procedure vs. Function: What’s the Difference?
Many PostgreSQL users confuse procedures and functions because other databases (like MySQL) use the terms interchangeably. In PostgreSQL, they are distinct:
- Functions (
CREATE FUNCTION) are called within SQL expressions (e.g.,SELECT my_function()) and return a value. They cannot commit or rollback transactions—the entire calling transaction controls the outcome. - Procedures (
CREATE PROCEDURE) are called withCALLand are not part of an expression. They can commit or rollback internal transactions, making them ideal for long ETL tasks or multi-step data updates.
If you need to return a value to a query, use a function. If you need transactional control or you’re performing a standalone operation, use a procedure.
How to Create a Stored Procedure: Step-by-Step
Here’s a minimal PL/pgSQL procedure that inserts a row into a hypothetical employees table:
- Connect to your database with psql or another client.
- Run the following code:
CREATE OR REPLACE PROCEDURE add_employee(
p_name TEXT,
p_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO employees (name, salary, hired_on)
VALUES (p_name, p_salary, CURRENT_DATE);
END;
$$;
Then call it:
CALL add_employee('Alice', 75000);
This procedure takes two input parameters and performs an insert. The CREATE OR REPLACE allows you to redefine the procedure without dropping it first.
Using Transaction Control in Procedures
The main advantage of procedures over functions is the ability to commit or roll back work inside the procedure. For example, suppose you want to transfer money between two accounts, and you must ensure both updates succeed or fail together:
CREATE OR REPLACE PROCEDURE transfer_funds(
p_from INT,
p_to INT,
p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
COMMIT; -- makes the changes permanent
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- undo both updates
RAISE;
END;
$$;
When you call CALL transfer_funds(1,2,100);, the procedure runs in its own transaction context. If the second update fails (e.g., due to a constraint), the exception handler rolls back the first update as well. This is impossible with a function, which would leave the transaction to the caller.
Common Syntax and Options
Here are the core elements of the CREATE PROCEDURE syntax:
- Parameters:
IN(default),OUT, andINOUT. Output parameters can return values to the caller, but they are not used like function returns. - LANGUAGE: Specify
plpgsql,sql,plpython3u, etc. The language must be installed and enabled. - Body: The procedural code is enclosed in
$$ ... $$(dollar quoting) to avoid escaping single quotes. - Security: By default, procedures run with the privileges of the invoker. Use
SECURITY DEFINERto run with the creator’s privileges—but do this only when necessary, as it can be a security risk. - Volatility: Procedures are always considered volatile; you cannot mark them immutable or stable.
Example with an OUT parameter:
CREATE PROCEDURE get_employee_count(OUT total INT)
LANGUAGE plpgsql AS $$
BEGIN
SELECT COUNT(*) INTO total FROM employees;
END;
$$;
CALL get_employee_count(NULL); -- pass a placeholder
Note: You must pass a NULL placeholder for OUT parameters when calling.
Overloading and Dropping Procedures
PostgreSQL allows multiple procedures with the same name but different argument types (overloading). For example, you can have add_employee(text, numeric) and add_employee(text, numeric, date). When you call CALL add_employee(...), PostgreSQL chooses the matching signature.
To remove a procedure, use:
DROP PROCEDURE add_employee(text, numeric);
You must specify the argument types to uniquely identify which procedure to drop.
Frequently Asked Questions
Can a stored procedure return a result set?
Yes, but not directly like a function. You can use RETURNS TABLE in a function, but a procedure can return result sets by using OUT parameters or by using REFCURSOR. In PL/pgSQL, you can open a cursor and return it to the caller, but the client must handle it. For most cases where you need a result set, a function is simpler.
How do I call a stored procedure from Java or Python?
From JDBC, use CallableStatement with the syntax {call add_employee(?, ?)}. From Python with psycopg2, use cursor.callproc('add_employee', ['Alice', 75000]). These drivers handle the CALL command internally.
What is the difference between CREATE PROCEDURE and CREATE FUNCTION in PostgreSQL?
Functions are used in SQL expressions, return a value, and cannot commit or rollback transactions. Procedures are invoked with CALL, can manage transactions, and are used for standalone operations. Choose functions for computations and procedures for multi-step data modifications.
Can I use IF statements and loops in a stored procedure?
Yes, if you use PL/pgSQL (or another procedural language). PL/pgSQL supports IF, CASE, LOOP, WHILE, FOR, and FOREACH loops, plus variable declarations and exception handling. This is why PL/pgSQL is the most common language for procedures.
Do stored procedures improve performance?
They can, by reducing network round trips between the application and database, and by allowing you to reuse query plans. However, the main benefit is code organization and encapsulation. In some cases, a poorly written procedure can be slower than a well-tuned SQL query. Always test with realistic data.
How do I debug a stored procedure?
Use RAISE NOTICE to print messages to the client, or use the pldebugger extension for step-by-step debugging in tools like pgAdmin. You can also wrap parts of the code in a block with EXCEPTION to log errors.
Related
Formula used
sel lang | Language | PL/pgSQL=1, SQL=2, PL/Python=3 in num | Number of parameters | - | 2 | 0 | 10 in body | Body length (lines) | - | 20 | 1 | 500 out est | Estimated execution time (ms) | ms | num * 2.5 + body * 0.8 | 1 out complexity | Relative complexity | - | (num + body/10) / 5 | 1
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Arctic Sea Ice Forecasts Disagree: What to Do
- MySQL Workbench to VPS: SSH Tunnel Setup
- Bluesky Beginners Guide: How to Get Started5.00 min/km
- LG V20 Won’t Turn On? Fixed
- Snagit vs Camtasia: Which One Do You Need?
- Scale a Small Business Without Losing Culture