Stored Procedure in PostgreSQL with Example
Introduction
A stored procedure in PostgreSQL is a powerful feature that allows developers to encapsulate SQL logic, control‑flow statements, and procedural code inside the database itself. Worth adding: by using a stored procedure, you can perform complex data manipulations, enforce business rules, and improve performance by reducing the amount of data sent over the network. This article explains what a stored procedure is, how to create and call it, and provides a practical example that you can adapt to your own projects No workaround needed..
What Is a Stored Procedure?
A stored procedure is a pre‑compiled collection of SQL statements stored in the database catalog. It lives on the server, so the client only needs to invoke it. Unlike a plain SQL query, a stored procedure can:
- Declare variables and parameters.
- Use control‑flow constructs such as
IF,LOOP, andWHILE. - Call other stored procedures or functions.
- Handle transactions explicitly.
In PostgreSQL, stored procedures are written in the PL/pgSQL language, which is PostgreSQL’s procedural extension to SQL Simple, but easy to overlook. Still holds up..
Why Use a Stored Procedure in PostgreSQL?
- Performance – The procedure is compiled once and can be cached, reducing parsing and planning overhead for repeated calls.
- Security – You can grant execution rights without giving direct table access, limiting exposure of sensitive data.
- Maintainability – Business logic is centralized, making updates easier and reducing duplication.
- Reusability – The same procedure can be called from multiple applications or scripts.
Steps to Create a Stored Procedure in PostgreSQL
Below is a step‑by‑step guide that you can follow in any PostgreSQL environment, including pgAdmin, psql, or any client that supports SQL execution.
Step 1: Connect to Your Database
\c my_database
Replace my_database with the name of the database where you want to create the procedure.
Step 2: Write the Procedure Definition
The basic syntax for a stored procedure is:
CREATE PROCEDURE procedure_name(
param1 datatype,
param2 datatype,
...
) LANGUAGE plpgsql AS
$
BEGIN
-- procedure body
END;
$;
procedure_name– the identifier you will use to call the procedure.param1,param2– optional input parameters; you can also define output parameters usingOUT.LANGUAGE plpgsql– tells PostgreSQL to compile the body using PL/pgSQL.- The
$delimiter marks the start and end of the procedure body.
Step 3: Define the Procedure Body
Inside the BEGIN … END; block you can write any combination of SQL statements, variable declarations, loops, conditionals, and calls to other procedures.
Step 4: Grant Execution Privileges
If the current user should be able to run the procedure, grant the EXECUTE privilege:
GRANT EXECUTE ON PROCEDURE procedure_name TO your_user;
Step 5: Call the Procedure
Use the CALL statement:
CALL procedure_name(param1_value, param2_value);
If the procedure has no parameters, simply CALL procedure_name; The details matter here..
Example: A Stored Procedure That Calculates Employee Salary Increments
Scenario
Imagine a company that wants to apply a salary raise of 10 % to all employees whose performance rating is ‘Excellent’. Instead of writing the update logic in each application, we will create a stored procedure that performs this operation atomically Small thing, real impact..
1. Table Structure
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary NUMERIC(12,2) NOT NULL,
performance VARCHAR(20) NOT NULL
);
2. Create the Stored Procedure
CREATE PROCEDURE apply_excellent_increment(
IN p_performance VARCHAR(20)
)
LANGUAGE plpgsql
AS $
DECLARE
v_salary_increment NUMERIC := 0.10; -- 10% raise
BEGIN
-- Update only employees with the specified performance rating
UPDATE employees
SET salary = salary * (1 + v_salary_increment)
WHERE performance = p_performance;
-- Optional: return the number of rows affected
RAISE NOTICE 'Updated % employees with performance %',
FOUND, p_performance;
END;
$;
Explanation of the code
- The procedure accepts a single input parameter
p_performance. - A local variable
v_salary_incrementis declared and set to0.10(10 %). - The
UPDATEstatement multiplies the current salary by1 + v_salary_incrementfor rows matching the performance rating. FOUNDis a built‑in PostgreSQL function that returns the number of rows affected by the last query; it is used insideRAISE NOTICEto provide feedback.
3. Populate Sample Data
INSERT INTO employees (name, salary, performance) VALUES
('Alice', 50000, 'Excellent'),
('Bob', 45000, 'Good'),
('Carol', 52000, 'Excellent'),
('Dave', 48000, 'Satisfactory');
4. Execute the Procedure
CALL apply_excellent_increment('Excellent');
You will see a notice similar to:
NOTICE: Updated 2 employees with performance Excellent
5. Verify the Results
SELECT emp_id, name, salary FROM employees;
Result:
| emp_id | name | salary |
|---|---|---|
| 1 | Alice | 55000.00 |
| 2 | Bob | 45000.Day to day, 00 |
| 3 | Carol | 57200. 00 |
| 4 | Dave | 48000. |
Alice and Carol, both with an Excellent rating, have received a 10 % raise.
Scientific Explanation of Stored Procedures
From a computational perspective, a stored procedure reduces the round‑trip cost between client and server. On top of that, when a client sends a single CALL statement, the database engine parses, plans, and executes the entire procedure on the server. This is especially beneficial in environments where network latency is high or where data volume is large Simple, but easy to overlook. No workaround needed..
On top of that, PL/pgSQL procedures run inside the transaction scope of the caller. If the client begins a transaction, the procedure’s changes become part of that transaction, ensuring ACID compliance. This atomic
ity guarantees that if any part of the procedure fails—whether due to a constraint violation, a deadlock, or an explicit ROLLBACK—the entire transaction is rolled back, preventing partial updates and preserving consistency. This is particularly important in multi-step operations where intermediate states could otherwise leave the database in an inconsistent condition.
Beyond atomicity, stored procedures offer several other advantages:
- Reusability and maintainability: Business logic centralised in a procedure can be invoked from multiple applications, reducing code duplication and ensuring consistent behaviour across clients.
- Security: By granting
EXECUTEpermission on a procedure without granting directINSERT/UPDATE/DELETErights on underlying tables, you enforce fine-grained access control. Users can perform only the operations the procedure allows. - Performance: As noted, parsing and planning happen once on the server. For complex operations involving multiple SQL statements, the reduction in network round-trips and repeated query compilation can be substantial.
- Encapsulation: The procedure hides implementation details, allowing schema changes (e.g., adding a column or altering a calculation) without breaking client code, as long as the procedure’s interface remains stable.
That said, stored procedures are not a silver bullet. They introduce a layer of procedural code that must be version-controlled, tested, and debugged like any other application logic. g.That said, , step-through debuggers, package managers) common in general-purpose languages. Overuse can lead to “fat databases” where business rules become scattered across both application and database tiers, complicating deployment and scaling. Worth adding: postgreSQL’s PL/pgSQL is powerful but lacks some of the tooling (e. Because of this, a balanced approach is recommended: use stored procedures for data-intensive operations, batch updates, and security-sensitive transactions, while keeping complex business workflows in the application layer where they can be more easily tested and evolved.
In the example above, apply_excellent_increment demonstrates a simple yet realistic use case: a periodic salary adjustment based on performance ratings. Consider this: by encapsulating the raise percentage and the update logic in a single procedure, administrators can apply consistent increments without writing ad-hoc SQL each time. The RAISE NOTICE provides immediate feedback, and the transaction scope ensures that either all eligible employees receive the raise or none do—a critical property for payroll operations It's one of those things that adds up..
Conclusion
Stored procedures remain a valuable tool in the PostgreSQL ecosystem, particularly for scenarios that demand atomicity, reduced network overhead, and centralised access control. In practice, the apply_excellent_increment example illustrates how a concise PL/pgSQL procedure can encapsulate a common business rule, execute efficiently on the server, and integrate without friction with the caller’s transaction. By understanding both the strengths (performance, security, reusability) and the trade-offs (maintainability, debugging complexity, potential for logic scattering), developers can make informed decisions about when to push logic into the database. Used judiciously, stored procedures contribute to solid, high-performance applications—especially in data-heavy environments where consistency and efficiency are essential Which is the point..